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

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.

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

StackExchange Database Administrators Q#164615, answer score: 6