I have two tables:
table1
: (ID, Code, Name)
table2
: (ID, Code, Name)
with same columns
I want to to insert data from table1 to table2 or update columns if that exists in table2 (table1.ID = table2.ID)
What is the simple way to do this?
WITH OUT MERGE
Advertisement
Answer
Merge table2 as target using table1 as source on target.id=source.id When matched Then update set target.id=source.id, target.name=source.name When not matched by Target Then INSERT (id, name) VALUES (id, name);
There are some issues with Merge statement,so it should be used with caution..
Further i recommend ,using merge as two seperate DML statements like below..
insert into table2 select * from table1 t1 where not exists (select 1 from table2 t2 where t2.id=t1.id) update t2 set t2.id=t1.id, t2.name=t1.name from table1 t1 join table2 t2 on t1.id=t2.id
Reasons being stated by Paul White here in his detailed answer..