Tuesday, December 8, 2009

Deleting records from a source table as you add them to a destination

Frequently we come across a case where the records in a source table must meet a certain condition in order to be loaded into the destination table.  I recently came across a problem where the records were to remain in the source table if the conditions were not met. 

So the question was how to keep track of the inserted records? 
  • Query the source table after the insert for records matching the destinationtable and then delete them? 
  • Create a #Temp table of what is to be inserted and join that same table to the source for deletion?
Or:
  • Use the OUTPUT clause (available on insert, update and delete statements) to populate a table of record-keys of what has been inserted and later use that to join to the source records for deletion
Here is an example:

CREATE TABLE #T1(COL1 INT)
CREATE TABLE #T2(COL1 INT)
CREATE TABLE #T3(COL1 INT)

DECLARE @iRec int

TRUNCATE TABLE #T1
TRUNCATE TABLE #T2
TRUNCATE TABLE #T3

-- populate some data 
set @iRec= 1
While @iRec <= 10
BEGIN
   INSERT #T1 VALUES(@iREC)
   if @iRec < 6
      insert #T2 values(@iREC)
   set @iRec = @iRec + 1
END

-- perform the deltion
DELETE #T1
      OUTPUT Deleted.*  INTO #T3
FROM #T1 ONE
JOIN #T2 TWO ON ONE.COL1 = TWO.col1

-show the results
SELECT '#T1 - original recs',* FROM #T1
SELECT '#T2 - deleted recs ',* FROM #T2
SELECT '#T3 - new table ',* FROM #T3

-- cleanup
DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3



No comments:

Post a Comment