Sql-server – Most efficient way to execute DELETE and INSERT operations in the same Table

sql serversql-server-2008

I have the following structure:

Table1              Table2 
C11, C12              C21 
 1   A                 A
 1   B                 B 
 1   E                 W
 2   F
 2   B
 3  ...

I need to execute the following two operations:

To delete those rows which are in Table1 but not in Table2 and C11 = @Parameter (In this case @Parameter = 1)

To insert those rows which are not present in Table1 but are present
in Table2

 Result
 Table1 
 C11, C12              
  1   A                
  1   B                 
  1   W
  2   F
  2   B
  3  ...

The simplest way to solve this is first deleteing all the records in Table1 and then inserting all records in Table2:

DELETE FROM Table1 WHERE C11 = @Parameter 
INSERT INTO Table1 SELECT @Parameter, C21 FROM Table2

Definetely I am looking for something more efficient, so I tried the following: 

DELETE t1 from Table1 t1 -- Delete
LEFT JOIN Table2 t2 ON t1.C12 = t2.C21 
WHERE t1.T11 = @Parameter AND t2.C21 IS NULL 

INSERT INTO Table1  
SELECT @Parameter, C21 from Table2 t2 -- INSERT 
LEFT JOIN Table1 t1 ON t1.C12 = t2.C21 AND t1.C11 = @Parameter
WHERE t1.C12 IS NULL

I am concern about performance because Table1 has millions of records. I think the join in both querys is a waste of resources.

I did try to create a select which could return both kind of records, those who need to be deleted and those who need to be inserted, but i did not succeed. I also tried using the MERGE operation but I think MERGE does not fit in this specifyc situation

Maybe someone has faced this problem before and can provide some advice.

I will appreciate any suggestion.

Best Answer

Using a merge statement is exactly what you need, but you can make it a little faster by using CTE's for the target and source tables. Putting an index on dbo.Table1 (C11, C12) should make this pretty fast as well.

;WITH CTE_Target AS 
    (
    SELECT T.C11, T.C12
    FROM dbo.Table1 AS T
    WHERE T.C11 = @Parameter
    )
, CTE_Source AS 
    (
    SELECT T.C21
    FROM dbo.Table2 AS T
    )
MERGE INTO CTE_Target  AS T
USING CTE_Source  AS S ON S.C21 = T.C12 AND T.C11 = @Parameter
WHEN NOT MATCHED BY TARGET THEN
    INSERT (C11, C12)
    VALUES (@Parameter, S.C21)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
Related Question