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,idThe old value of chrThe new value of chrIn 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?

Ask Question

Ask a Question

Ask Question

Corporate Training

SQL Server

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

Asked by Aashish Chaursiya May 15, 2021 821 views 1 answer

About this question

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?

Your answer

1 Answer

Best Answer · By JanBask MS SQL Server Expert

Aashish Chaursiya

Answered on May 15, 2021

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

Was this helpful?