CODEWITHSUNDEEP
sqlsql-server
Dozer
Dozer

Reputation: 5245

How to copy or update from one table to another table

I have two tables. user and user_new the user contains the old data. the user_new contains the new data. I want to sync the user_new to user.

  1. if the data exist in user_new and not exist in user,then insert to user.
  2. if the data exist in user and user_new, then update.(compare with the column id)

what's the fast sql to do it?

Upvotes: 0

Views: 78

Answers (4)

Sundar G
Sundar G

Reputation: 1069

You can't do insert and update in a single query you have to do in seperate

select * from user where user_id not in (select user_new.user_id from user_new )

this query results the data for insert query similarly u have to update by replacing not in to in

Upvotes: 0

praveen
praveen

Reputation: 12271

From Sql Server 2008 onwards you can use Merge syntax 

MERGE user target
USING user_new source
ON taget.ID = source.ID
WHEN MATCHED THEN
UPDATE
SET target.Column= source.Column1,target.column2=source.column2
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID,Column1,Column2)
VALUES (source.ID,source.column1,source.column2);

or you can use the below query 

INSERT INTO user(ID,column1,column2) 
SELECT ID,column1,column2 FROM user_new  AS source
WHERE NOT EXISTS (SELECT * FROM user WHERE ID = source.ID);

UPDATE target SET ...
FROM user AS target
INNER JOIN user_new AS source
ON target.ID = source.ID;

Upvotes: 1

Devart
Devart

Reputation: 122022

This works on any server version -

   -- 1) Insert new record

        INSERT INTO old_table(id, column)
        SELECT n.id, n.column
        FROM new_table n
        LEFT JOIN old_table o ON n.id = o.id
        WHERE o.id IS NULL

   -- 2) Update existed record

        UPDATE o
        SET column = n.column
        FROM old_table o
        JOIN new_table n ON n.id = o.id

Upvotes: 2

Yohn
Yohn

Reputation: 17

Sounds like you might need a Merge if you have sql server 2008+.

Upvotes: 0

Related Questions