Upsert from select

Question

I need to perform an upsert from select like this but in SQL Server. I found someone doing an upsert in SQL Server here, but that's not from a select.

My query basically looks like this right now:

INSERT INTO (table1) (...)
    SELECT (...)
    FROM (table 2)
    WHERE X NOT IN (SELECT Y from (table1) WHERE Y IS NOT NULL)

But I can't figure out how to add the update part. I would like to add the update part after since 99% of the time it will be an insert.

Edit: this code would work if I was able to do it from a SELECT:

BEGIN TRY
    INSERT INTO table1 (id, name, itemname, itemcatName, itemQty)
    VALUES ('val1', 'val2', 'val3', 'val4', 'val5')
END TRY
BEGIN CATCH
    UPDATE table1 
    SET name = 'val2', 
        itemname = 'val3', 
        itemcatName = 'val4', 
        itemQty = 'val5'
    WHERE id = 'val1'
END CATCH

One way would be to have an IF condition and either perfom and Update or an Insert based on whether the record exists already or not. Another way would be to use [MERGE](https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017). — rigerta, Jul 04 '18 at 12:58
@ARr0w yes exacly but ideally I would like to do insert first and update if duplicate since I will be working with alot of records and most of the time it will be a simple insert — Chsir17, Jul 04 '18 at 13:00
Why not using `MERGE`? It's made for that purpose, and is probably more efficient. — Patrick Honorez, Jul 04 '18 at 13:16
I would use Merge with [caution as some bugs still exist](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) — Mazhar, Jul 04 '18 at 13:17
I'm not clear what you mean by "if i was able to do it from a SELECT". What are you "selecting" from at present? Another table? — Mazhar, Jul 04 '18 at 13:19
[Here](https://stackoverflow.com/questions/2479488/syntax-for-single-row-merge-upsert-in-sql-server) are a good MERGE example for a similar issue — Ulrik, Jul 04 '18 at 14:09

score 1 · Answer 1 · answered Jul 04 '18 at 13:00

Kind the same line of thinking, I guess:

update t1
set ......
from table1 t1
inner join table2 on t1.Y=table2.X

Notice the mandatory use of alias for the updated table, in order to update based on a join.

Alternative, you coud look around (or wait for an answer) for MERGE. It is very elegant to do things like upsert with one statement, but I haven't concluded if probable downsides are worth it.

score 0 · Answer 2 · answered Jul 04 '18 at 13:26

0

update table1 
  set name = 'val2', itemname = 'val3', itemcatName = 'val4', itemQty = 'val5'
  where id = 'val1'

IF @@ROWCOUNT = 0
insert into table1(id, name, itemname, itemcatName, itemQty)
  values('val1', 'val2', 'val3', 'val4', 'val5')

Do an UPSERT: http://en.wikipedia.org/wiki/Upsert

answered Jul 04 '18 at 13:26

Dinesh

102
7

Is there a way to do both update and inserts from a select? i know you can do: insert into (...) select, but how to you do the update part? – Chsir17 Jul 04 '18 at 13:35
Then you have to use MERGE but that will degrade performance. – Dinesh Jul 04 '18 at 13:41

score 0 · Answer 3 · answered Jul 04 '18 at 13:54

If you want to INSERT if a record doesn't exist or UPDATE if it does then try this

IF EXISTS (SELECT 1 FROM table1 WHERE somecol = @someval)
BEGIN
    UPDATE T1
    SET T1.someCol1 = T2.someCol1
        ,T1.someCol2 = T2.someCol2 
         --, etc,  etc
    FROM table1 T1
    INNER JOIN table2 T2 ON T1.Id = T2.T1_FK
END
ELSE
BEGIN
    INSERT INTO table1 (someCol, someCol2 --,etcetc)
    SELECT T2.someCol1, T2.someCol2 --etc etc
    FROM table2 T2
    WHERE somecol = @someval
END

score 0 · Answer 4 · answered Jul 04 '18 at 14:03

0

Do the update first

update t1
SET (...)
FROM (table2) t2
JOIN table1 t1 WHERE t1.Y = t2.X


INSERT INTO (table1) (...)
SELECT (...)
FROM (table2) t2
WHERE NOT EXISTS (SELECT 1 from table1 WHERE Y = t2.X)

answered Jul 04 '18 at 14:03

paparazzo

42,665
20
93
158

Upsert from select

4 Answers4