How do I do an UPSERT in SQL Server, returning the pre-updated values?

244 Asked by Aashishchaursiya in SQL Server , Asked on May 15, 2021

All of the syntax is valid PostgreSQL as an example of an UPSERT that returns the old and new values for the field. Let's say I have a table foo with (1,A)...(5,E). CREATE TEMP TABLE foo AS SELECT id, chr(id+64) FROM generate_series(1,5) AS t(id); CREATE UNIQUE INDEX ON foo(id); id | chr ----+----- 1 | A 2 | B 3 | C 4 | D 5 | E (5 rows) Now, let's say I want to UPSERT 6 rows. A few colliding, a few new rows. SELECT id, chr(id+74) FROM generate_series(3,8) AS t(id); id | chr ----+----- 3 | M 4 | N 5 | O 6 | P 7 | Q 8 | R How would I get,

id
The old value of chr
The new value of chr
In PostgreSQL, I would do..

WITH t1 AS ( SELECT id, foo.chr AS oldchr, chr(id+74) FROM generate_series(3,8) AS t(id) LEFT OUTER JOIN foo USING (id) ), tupdate AS ( UPDATE foo SET chr = t1.chr FROM t1 WHERE foo.id = t1.id RETURNING foo.id, t1.chr, t1.oldchr ), tinsert AS ( INSERT INTO foo (id, chr) SELECT id, chr FROM t1 WHERE t1.oldchr IS NULL RETURNING id, chr, null::text ) SELECT * FROM tupdate UNION ALL SELECT * FROM tinsert; id | chr | oldchr ----+-----+-------- 3 | M | C 4 | N | D 5 | O | E 6 | P | 7 | Q | 8 | R | (6 rows)That table above is returned by those CTEs. And, that's the output I'm looking for. What is SQL Server UPSERT? SQL server upsert is used for?

Answered by ankur Dwivedi

SQL Server Upsert is used for inserting a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. You can use merge with the output clause.

Temp table:

  create table #foo ( id int unique, chr char(1) ); insert into #foo(id, chr) values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');

Merge:

  with t1 as ( select T.id, T.chr from (values (3, 'M'), (4, 'N'), (5, 'O'), (6, 'P'), (7, 'Q'), (8, 'R')) as T(id, chr) ) merge #foo as T using t1 as S on T.id = S.id when not matched then insert (id, chr) values(S.id, S.chr) when matched then update set chr = S.chr output inserted.id, inserted.chr, deleted.chr as oldchr;

Result: id chr oldchr ----------- ---- ------ 3 M C 4 N D 5 O E 6 P NULL 7 Q NULL 8 R NULL

How do I do an UPSERT in SQL Server, returning the pre-updated values?

Your Answer