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?
- 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
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