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 eliminate the rows that are in Table 1 but not in Table 2 and C11 = @ Parameter (in this case, @ Parameter = 1)
To insert those rows that are not present in Table1 but are present
in Table 2
Result Table 1 C11, C12 1 A 1 B 1 W 2 F 2 B 3 ...
The easiest way to solve this is by first removing all the records in Table 1 and then inserting all the records in Table 2:
DELETE FROM TABLE1 WHERE C11 = @Parameter INSERT IN TABLE1 SELECT @ Parameter, C21 FROM TABLE2
I'm definitely looking for something more efficient, so I tried the following:
DELETE t1 from Table1 t1 - Delete LEFT GASKET Table2 t2 ON t1.C12 = t2.C21 WHERE t1.T11 = @Parameter AND t2.C21 IS NULL Insert in table 1 SELECT @ Parameter, C21 from Table2 t2 - INSERT LEFT GASKET Table1 t1 IN t1.C12 = t2.C21 AND t1.C11 = @Parameter Where t1.c12 is null
I'm worried about performance because Table1 has millions of records. I think the union in both consultations is a waste of resources.
I tried to create a selection that could return both types of records, those that need to be deleted and those that need to be inserted, but I did not succeed. I also tried to use the MERGE operation but I think MERGE does not fit into this specific situation
Maybe someone has faced this problem before and can offer some advice.
I will appreciate any suggestion.