Search code examples
sqlsql-serversql-server-2005

Update and insert to one table from another

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

Solution 
  • 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..