Insert into select, use inserted Id to update column in selected table

Question

I'm trying to copy data from table A to table B, and after it's inserted update FK column in table A to newly inserted table B Id. Because theese needs to be related.

INSERT INTO TableB(ColA, ColB, ColC, ColD, ...) 
SELECT a.ColA, a.ColB, a.ColC, a.ColD, ...
FROM TableA a for update;
update a set TableBId = (newly inserted id from table B)

In code for better understanding

        foreach (var tableA in AllTableA)
        {
            TableB newB = new TableB(tableA);
            AllTableB.Add(newB);
            tableA.TableBId = newB.GetId();
        }

How can I do this in one transaction?

Does this answer your question? [Get the last inserted row ID (with SQL statement)](https://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement) Use `SCOPE_IDENTITY()`. Or for multiple rows, use an `OUTPUT` clause — Charlieface, Sep 30 '21 at 10:06
`for update` is not valid in SQL Server. You can use `WITH (UPDLOCK)` if necessary — Charlieface, Sep 30 '21 at 10:07
@Charlieface no, since I want to get every Id from every insertion form the select, and update each from that was selected and not lose the relation between them. for each row that was selected I want to create a relation with the inserted row that got values from the selected row. — LittleMygler, Sep 30 '21 at 11:52
Use an `OUTPUT` clause into a temp table or table variable, see also https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — Charlieface, Sep 30 '21 at 11:58
Yes I understand that, but I'm inserting like 600 rows from 600 selected values without loosing connection to eachother. I can easily do this with one simple insert but I can't get it to work with an insert with OUTPUT into select and update, for each row. — LittleMygler, Sep 30 '21 at 12:17
Something like `OUTPUT inserted.Id, inserted.SomeMatchingColumn INTO @tempTable` then a joined update `UPDATE a SET TableBId = t.Id FROM tableA JOIN @tempTable t ON...` — Charlieface, Sep 30 '21 at 12:23

LittleMygler · Answer 1 · 2021-09-30T12:24:21.190

I solved it by:

I added a temporary ID column to table A and during the insert i gave it the value from tableB Id.

I then updated TableB.FK from where the Id was found in tableA.

Then removed the column.

INSERT INTO TableA(values..., tableBId)
SELECT Values..., Id
FROM TableB

Update TableB SET TableAId = tableA.Id
FROM TableB
INNER JOIN TableA ON(Tablea.TableBId = TableB.Id)

ALTER TABLE TableA
DROP COLUMN TableBId;

Maybe not the best way, but it worked.

Insert into select, use inserted Id to update column in selected table

1 Answers1