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?

WHITOUT MERGE

回答1:

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

回答2:

Assuming the ID column is unique and should not be set, it seems you could do it in two SQL Statements.

/* UPDATE the rows in TABLE2 */
UPDATE TABLE2
    SET NAME = (SELECT NAME FROM TABLE1 WHERE TABLE1.CODE = TABLE2.CODE)
WHERE CODE IN (SELECT CODE FROM TABLE1)

/* INSERT the rows that are missing */
INSERT INTO TABLE2
    (CODE, NAME)
    (
         SELECT CODE, NAME
         FROM TABLE1
         WHERE CODE NOT IN (SELECT CODE FROM TABLE2)
    )

回答3:

MERGE table2 t2
USING table1 t1
ON t1.ID = t2.ID
WHEN MATCHED THEN
  UPDATE
  SET t2.Code = t1.Code, t2.Name = t1.Name 
WHEN NOT MATCHED BY TARGET THEN
  INSERT (ID, Name, Code)
  VALUES (t1.ID, t1.Name, t1.Code);

回答4:

get all rows that are in table1 but not in table2

insert into table2(id, code, name)(
SELECT table1.*
FROM table1
    LEFT JOIN table2 ON (table1.id = table2.id)
WHERE table2.C IS NULL
)

update table2

update table2 set name = (select name from table1 where table1.code = table2.code and table1.id = table2.id)

It might be worth looking at triggers on update and insert if you'd like to have this done manually

回答5:

Here I am writing a script that use full when you want to update table2 from table1.

    Update table2 set table2.code = table1.code, table2.name=table1.name from table1 where table2.id=table1.id

And if you want to insert then use this script.

Insert into table2  (id,code,name) select id,code,name from table1

If in table2 id is not auto increment. Otherwise not insert the value of id column in table2.