Query to Insert into Table1 Then update Table2 using inserted Identity

Question

Can you help me create a Query to Insert into Table1 Then update Table2 using inserted Identity?

As with the title, but I need it in SQL Server. I need only to do it once so I want to do this without using triggers. I only found how to do it in PostGres, but I need it in TSQL. I don't know PostGres SQL but I used what I found to create the query below, which is supposed to be in PostGres, but I need it in SQL Server.

    WITH ins  AS (
    INSERT INTO [dbo].[tbl1]
       ([Col1]
       ,[Col2])
       SELECT 
           [Col1]
          ,[Col2]
      FROM [dbo].[Tbl2]
      where ([Tbl2].[Tbl1Id] = 0 OR [Tbl2].[Tbl1Id] is null)
      GROUP BY [Col1], [Col2]
      RETURNING Tbl1Id, Col1, Col2
    )
    UPDATE [Tbl2]
    SET [Tbl2].[Tbl1Id] = ins.[Tbl1Id]
    WHERE [Tbl2].[Col1] = ins.[Col1]
        AND [Tbl2].[Col2]= ins.[Col2]

It's also not clear what your question is; you don't actually *ask* anything. — Thom A, Jun 15 '23 at 10:21
@ThomA That is in Postgress, but I don't know how to do it in T-SQL. I need it in T-SQL. — Ray Lionfang, Jun 15 '23 at 10:27
How much research have you done? for example [this question & answer](https://stackoverflow.com/questions/65627579/postgresql-returning-clause-vs-microsoft-sql-server) explains the SQL Server equivalent — Stu, Jun 15 '23 at 10:30

score 3 · Accepted Answer · answered Jun 15 '23 at 11:09

There's no returning keyword after the insertion or update, instead of it you can use the OUTPUT keyword, another point is you can't insert/update inside of CTE.

You can first create a temporal/variable table and then when inserting it into tbl1 export the output of the statement into the temporal/variable table(https://stackoverflow.com/a/6292193/4818540) and then use that temporal/variable table for updating your second table.

begin

DECLARE @InsertedIds TABLE (
    Tbl1Id INT,
    Col1   VARCHAR(50),
    Col2   VARCHAR(50)
);

INSERT INTO dbo.tbl1 (Col1, Col2)
OUTPUT inserted.Tbl1Id, inserted.Col1, inserted.Col2 INTO @InsertedIds
SELECT Col1, Col2
FROM dbo.Tbl2
WHERE (Tbl2.Tbl1Id = 0 OR Tbl2.Tbl1Id IS NULL)
GROUP BY Col1, Col2;

UPDATE T2
SET T2.Tbl1Id = I.Tbl1Id
FROM dbo.Tbl2 T2
JOIN @InsertedIds I ON T2.Col1 = I.Col1 AND T2.Col2 = I.Col2;

end;

You're welcome!@RayLionfang – Farshid Shekari Jun 15 '23 at 11:18 — Farshid Shekari, Jun 15 '23 at 11:18

Query to Insert into Table1 Then update Table2 using inserted Identity

1 Answers1