question

I'd go for either a CTE (SQL 2005 and above) or MERGE (SQL 2008 and above). Merge should be more efficient, as it will do a scan on both tables, merge join them and delete what is necessary. The CTE would probably perform well, but the execution on my test showed it to do a loop join, which would be more costly - this could be caused through the lack of statistics on my example table variable. ***Both of my solutions join to the archive table though, ensuring that you are only deleting rows that really are in the archive.*** Example 1 - CTE to find the rows to delete: ---------------------------------------- /* Create Test Tables */ DECLARE @LiveTable AS TABLE (Col1 int) DECLARE @ArchiveTable AS TABLE (Col1 int) /* Fill test tables */ INSERT INTO @LiveTable (Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 INSERT INTO @ArchiveTable (Col1) SELECT 1 UNION ALL SELECT 2 /* Declare maximum number of rows to be processed */ DECLARE @BatchSize int = 1000 /* Delete the data that exists in Archive and LiveTable */ ; WITH toDelete AS (SELECT TOP (@BatchSize) L.* FROM @Livetable L WHERE EXISTS ( SELECT * FROM @ArchiveTable A WHERE L.Col1 = A.Col1 )) DELETE FROM toDelete ; /* Display the rest of @LiveTable*/ SELECT * FROM @LiveTable Example 2 - MERGE command (SQL 2008 and newer): ---------------------------------------- /* Create Test Tables */ DECLARE @LiveTable AS TABLE (Col1 int) DECLARE @ArchiveTable AS TABLE (Col1 int) /* Fill test tables */ INSERT INTO @LiveTable (Col1) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 INSERT INTO @ArchiveTable (Col1) SELECT 1 UNION ALL SELECT 2 /* Declare maximum number of rows to be processed */ DECLARE @BatchSize int = 1000 /* Delete the data that exists in Archive and LiveTable using MERGE */ ; MERGE @LiveTable tgt USING (SELECT TOP (@BatchSize) * FROM @ArchiveTable AT) src ON tgt.Col1 = src.Col1 WHEN MATCHED THEN DELETE ; /* Display the rest of @LiveTable*/ SELECT * FROM @LiveTable