0

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.

  • If your "The simplest way ..query fulfill your requirement then that is the best query. – KumarHarsh Dec 1 '18 at 17:52
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;

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.