Updated SQL Conditional INSERT

Question

I have a situation where I need to insert data from table1 to table2. Before insert check if a certain row already exist in the table2, if it does then just update col2, col4 of the row. If it doesn't exist then insert a new row.

I am using SQLSERVER 2008 R2. How could I achieve this?

The situation has changed a bit now. I need something like this.

DECLARE @table1 TABLE 
(id int not null, ahccs int not null, info varchar(25), flag varchar(2))
DECLARE @table2 TABLE 
(id int not null, ahccs int not null, info varchar(25), flag varchar(2))

INSERT INTO @table1
VALUES(1, 1223, 'et', 'X')
INSERT INTO @table1
VALUES(2, 321, 'et', 'X')
INSERT INTO @table1
VALUES(3, 134, 'et', 'X' )
INSERT INTO @table1
VALUES(4, 168, 'et', 'X' )
INSERT INTO @table1
VALUES(5, 123, 'et', 'X' )


INSERT INTO @table2
VALUES(1, 1223, 'dt', 'y' )
INSERT INTO @table2
VALUES(2, 456, 'dt', 'y' )
INSERT INTO @table2
VALUES(3, 123, 'dt', 'y' )
INSERT INTO @table2
VALUES(4, 193, 'dt', 'y' )
--SELECT * FROM @table1

SELECT * FROM @table2

MERGE
INTO    @table2 t2
USING   @table1 t1
ON      t2.id = t1.id or t2.ahccs = t1.ahccs
WHEN NOT MATCHED THEN
UPDATE
SET     flag = 'z'
INSERT VALUES (100, t1.ahccs, t1.info, 'l');

The two issues I am having are: 1) Merge doesn't support multiple steps, I believe. 2) Update is not allowed in WHEN NOT MATCHED case.

Please advise.

Thank You.

possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — Tim Schmelter, Apr 25 '12 at 20:55
Are you using SQL 2008 or higher? If so, a MERGE statement would do the business — GShenanigan, Apr 25 '12 at 20:57
This is often referred to as doing an "upsert". You can Google that. — DOK, Apr 25 '12 at 20:59

score 3 · Answer 1 · answered Apr 25 '12 at 20:59

You need to use merge, it lets you match the data that you are trying to update or insert ("upsert") against the data that is currently in the table, and perform different actions based on the presence or absence of a match.

MERGE Stock S
USING Trades T ON S.Stock = T.Stock
WHEN MATCHED THEN
    UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN
    INSERT VALUES (Stock, Delta);

This example is from here.

score 2 · Accepted Answer · answered Apr 25 '12 at 20:59

2

MERGE
INTO    table2 t2
USING   table1 t1
ON      t2.id = t1.t2_id
WHEN NOT MATCHED THEN
INSERT  
VALUES  (t1.col1, t1.col2, ...)
WHEN MATCHED THEN
UPDATE
SET     col2 = t1.col2,
        col4 = t1.col4

answered Apr 25 '12 at 20:59

Quassnoi

413,100
91
616
614

Updated SQL Conditional INSERT

2 Answers2