CODEWITHSUNDEEP
sql-serversql-server-2008
user2687593
user2687593

Reputation: 1

compare 2 tables if match update if not match insert

I have 2 tables with same table structure. Table A is having all transaction with 3 unique key in each record. Table B have only condition base record only.

I want compare both tables if Table B has matching record than I want to update and Table B have not matching record than insert in Table B.

Can you please suggest best way to do it like ssis or any thing else

Upvotes: 0

Views: 1467

Answers (1)

Monty Wild
Monty Wild

Reputation: 3991

The easiest way is a MERGE statement:

MERGE INTO Table_B  
USING Table_A 
ON TableA.ID1 = Table_B.ID1 AND TableA.ID2 = Table_B.ID2 AND TableA.ID3 = Table_B.ID3
WHEN MATCHED THEN UPDATE SET A = Table_A.A, B = Table_A.B         -- Etcetera...
WHEN NOT MATCHED THEN INSERT (A, B) VALUES (Table_A.a, Table_A.B) -- Etcetera...
WHEN NOT MATCHED BY SOURCE THEN DELETE                            -- If Necessary...
;

By the way, don't forget the ";" at the end. SQL Server doesn't usually need them, but a MERGE does.

Upvotes: 1

Related Questions