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.