Multiple DML statements inside WITH (CTE) query

Sean Smith 21 Reputation points
2021-08-01T16:49:32.807+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,869 questions
0 comments No comments
{count} votes

Accepted answer
  1. Sam of Simple Samples 5,516 Reputation points
    2021-08-01T17:21:27.507+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful