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.
