Using MERGE in SQL

Question

I have two tables- T1, T2 with identical table structure. I want to insert new rows in T2 from T1 if they don't already exist in T2, and also update the existing data in T2 on the primary key may be. Can someone help me with the SQL for this please using MERGE.

TIA

Liya Tansky · Answer 1 · 2016-06-12T06:17:36.457

0

since you gave no schema and assuming id is not identity:

create table t1 (id int, txt varchar(5))
create table t2 (id int, txt varchar(5))

merge t2 as tgt
    using t1 as src on tgt.id = src.id
when not matched by tgt then
    insert (id,txt)
    select src.id, src.txt;

edited Jun 12 '16 at 06:17

answered May 07 '16 at 16:17

Liya Tansky

249
1
8

sorry I forgot to put the ; at the end of the statement, corrected – Liya Tansky May 07 '16 at 16:22
can you put a schema of T1 and T2 in your comment? – Liya Tansky May 07 '16 at 17:41
sorry i am not too good at SQL so need to need exactly how the statement should look like – Newbie Jun 11 '16 at 13:14
The code creates to tables with same columns, and then you have the MERGE statement that you asked. – Liya Tansky Jun 12 '16 at 06:17

score 0 · Answer 2 · answered Dec 10 '18 at 15:40

Assuming the table name for destination is T2 and source is T1 For dummy table lets see assume both table have two column -code and description( with code as primary key). Below is the code PS-The semicolon is compulsory with Merge query

 Merge T2 as T
 USING T1 AS S
 ON T.Code=S.Code
 WHEN MATCHED
 THEN UPDATE SET description=S.description
 WHEN NOT MATCHED BY TARGET
 THEN INSERT (T.code,T.description) VALUES (S.code,S.description);
 --WHEN NOT MATCHED BY SOURCE
 -- THEN DELETE;

Using MERGE in SQL

2 Answers2