Most efficient way to execute DELETE and INSERT operations in the same Table

Question

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.

• KumarHarsh: If your "The simplest way ..query fulfill your requirement then that is the best query.

Answer 1

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;