I have the following structure:
C11, C12 C21
1 A A
1 B B
1 E W
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
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.