SQL : Write MERGE instead of Truncate and Reload table

Question

Hi Team -

Truncate TABLE A

INSERT INTO TABLE A ( Column1, Column12,Column13,Column14,Column15,Column16,Column17,Column18)

Select Column1, Column12,Column13,Column14,Column15,Column16,Column17,Column18 FROM OPENQUERY(ORACLE,' SELECT Column1, Column12,Column13,Column14,Column15,Column16,Column17,Column18 FROM OracleTable')

This is my current query but i got a request not to truncate the table until i see a change, insert records, update etc..

So could you please help me how to write the MERGE script in this case ?

Answer 1

Hi @kkran

Try this:

MERGE TableA AS tgt  
USING (Select Column1, Column12,Column13,Column14,Column15,Column16,Column17,Column18 
       FROM OPENQUERY(ORACLE,' SELECT Column1, Column12,Column13,Column14,Column15,Column16,Column17,Column18 FROM OracleTable')) as src  
ON (tgt.Column1 = src.Column1) 
WHEN MATCHED 
    THEN UPDATE 
	SET Column12=src.Column12,
	    Column13=src.Column13,...   
WHEN NOT MATCHED BY TARGET
    THEN INSERT (Column1, Column12,Column13,Column14,Column15,Column16,Column17,Column18)  
         VALUES (src.Column1,src.Column12,src.Column13,src.Column14,src.Column15,src.Column16,src.Column17,src.Column18)  
WHEN NOT MATCHED BY SOURCE
    THEN DELETE  
OUTPUT $action, Inserted.*, Deleted.*;  

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.