I'm working on a Data Warehouse implementation using SSIS 2008. The source system is in SQL 2000. The code to find deleted records and insert them into a deleted records table is as follows: INSERT INTO MDA.DeleteRecords (TableID, RecordID) SELECT Deltd.TableID TablID, NUM.NumberList FROM MDA.Numbers NUM LEFT OUTER JOIN dbo.slcdpm SLC ON NUM.NumberList = SLC.identity_column LEFT OUTER JOIN (SELECT DR.TableID, DR.RecordID, DR.DateDeleted FROM MDA.DeleteTables DT INNER JOIN MDA.DeleteRecords DR ON DR.TableID = DT.TableID WHERE DT.TableName = 'slcdpm') Deltd ON Deltd.RecordID = NUM.NumberList WHERE SLC.identity_column IS NULL AND NUM.NumberList <= IDENT_CURRENT ('slcdpm') AND Deltd.TableID IS NULLBasically I am using Left Outer Join (where null) twice to find records that exist in a numbers table, but don't exist in the source table, nor are already listed as deleted in the DeleteRecords table. I'd love to call this whole thing from one stored procedure and just pass in the source table name. However, I know you shouldn't do that and no performance benefit will be gained by using a SP to run dynamic SQL.I have approximately 300 tables which will have to be checked this way. Should I actually create 300 separate stored procedures, one for each table? Use SQL that is dynamically generated in SSIS to pull newly deleted records per source table? I'd love to use delete triggers, but that won't be allowed on this project. Any suggestions?
Finding Deleted Records. Use Stored Procs?
Posted - 2011-06-18 : 15:06:54
