Update and insert to one table from another

Question

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

Possible duplicate of: http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update — Michael L., May 12 '16 at 14:12
You merge table 1 into table 2, update where matched and insert where not - https://msdn.microsoft.com/en-GB/library/bb510625.aspx — MightyRearranger, May 12 '16 at 14:13
Then you may have to do it in two steps: 1) Insert missing rows from `table2` into `table1`, 2) update rows that don't match. As a side note, is there any particular reason you need two identical tables? — Michael L., May 12 '16 at 14:20
yes, Im inserting and updating into table1 many times then I need to do that operations at once to table2 — Hayk Melkonyan, May 12 '16 at 14:24
I don't think you're going to find a solution that will allow you to insert and update in a single operation in SQL Server 2005. — Michael L., May 12 '16 at 14:26

score 5 · Accepted Answer · edited May 23 '17 at 12:02

5

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

edited May 23 '17 at 12:02

Community

1
1

answered May 12 '16 at 14:22

TheGameiswar

27,855
8
56
94

Thank you for the suggestion.Any obvious reasons.. or pointers..? – TheGameiswar May 12 '16 at 14:43
it is obvious reason. if you insert first then you update freshly inserted records as well ;) – Alex Kudryashev May 12 '16 at 14:44
I would agree. Use update first. Also, I would wrap the insert/update (2 statements) in a TRANS. – granadaCoder May 12 '16 at 16:04

score 1 · Answer 2 · answered May 12 '16 at 14:21

1

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);

answered May 12 '16 at 14:21

Joe C

3,925
2
11
31

score 0 · Answer 3 · answered May 12 '16 at 14:20

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

score 0 · Answer 4 · answered May 12 '16 at 14:29

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

score 0 · Answer 5 · edited May 19 '21 at 02:30

Inserting Date into target table once date is updated in source table Here there is a working example:

create table Table1(id int,name varchar(100));
create table Table2(id int,name varchar(100));
create trigger Table1Trigger after insert on Table1 for each row begin
    insert into Table2(id, name) values (new.id, new.name);
end;

Use the following queries to validate the results

insert into Table1 values(1,'John'),(2,'Smith'),(3,'Carol');

B. P. Singh · Answer 6 · 2016-05-13T06:18:09.033

-1

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.

edited May 13 '16 at 06:18

answered May 12 '16 at 14:41

B. P. Singh

13
4

Update and insert to one table from another

6 Answers6

Linked