Multiple DML statements inside WITH (CTE) query

Question

In PostgreSQL you can do this:

WITH querycte AS (...
), test AS (
  SELECT func1() WHERE EXISTS (SELECT true FROM querycte)
), updated AS (
  UPDATE some_table SET column = value WHERE...
  RETURNING id
), inserted AS (
  INSERT INTO some_other_table
  SELECT scalar1, scalar2, id
  FROM updated
)
SELECT 
  (SELECT COUNT(*) FROM updated) AS updated_count,
  (SELECT COUNT(*) FROM inserted) AS inserted_count;

How would you do this in SQL Server? SQL Server does not appear to support the RETURNING clause, which makes getting id values from initial DML into subsequent DML impossible?

Answer 1

See RETURNING Clause in INSERT for C# - Oracle to SQL Server Migration - SQLines Tools. It says:

In SQL Server, you can use the OUTPUT clause in a INSERT statement to return the assigned ID.