How to use autogenerated ID from first INSERT in a second INSERT

Question

I want to insert data into a table and use the auto-generated ID from the first insert for another table insert.

Currently this is what I have:

Insert into InventoryTransfer (CFromSite, CToSite, CStatus, CUsername)
values ('Store', 'Warehouse', 'Active', 'Chris');

Table InventoryTransfer has a column called CID and it's auto-incrementing. What I currently do is I have to execute

SELECT @@IDENTITY;

and get the value to do the statement below:

Insert into InventoryTransferDetails (CTransferID, CQuantity, CBarcode) 
values (6, 3, '734987598345');

Where 6 is the autogenerated ID I got from SELECT @@IDENTITY;. I'm doing these 3 steps on application level and by that I mean my application has to connect to the server multiple times. How can I do this with just 1 contact with the server?

My logic would be:

Insert into InventoryTransfer (CFromSite, CToSite, CStatus, CUsername)
values ('Store', 'Warehouse', 'Active', 'Chris');

Select @@IDENTITY;

Insert into InventoryTransferDetails (CTransferID, CQuantity, CBarcode) 
values (@@IDENTITY, 3, '734987598345');

Something like the above that would be done in 1 connection with the server. I don't mind using ; as long as it's only 1 connection to the server. Thank you so much.

Don't use `@@IDENTITY`, use `SCOPE_IDENTITY()` instead because `@@IDENTITY` will return unexpected results if triggers are in-use: https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide — Dai, Jul 15 '23 at 05:04
_"Something like the above that would be done in 1 connection with the server. I don't mind using ; as long as it's only 1 connection to the server.."_ - you're conflating ttransactions with sessions and connections - as for semicolons: they should always be used to terminate every statement, as per Microsoft's recommendations), why do you seem to have a dislike of them? — Dai, Jul 15 '23 at 05:06
_"...the application level and by that I mean my application has to connect to the server multiple times"_ - please show us your client code - it sounds like you don't know how to specify a SQL batch in a single TDS command/request - or you're using a horribly limited library that doesn't allow for that for some reason? — Dai, Jul 15 '23 at 05:07
@Dai I don't have a dislike for semicolons. I just meant that the SQL statements don't have to be all with just 1 semicolon. Kind of like multiple INSERTs can be done with multiple semicolons or with multiple commas. — chris_techno25, Jul 16 '23 at 01:08

score 1 · Accepted Answer · answered Jul 15 '23 at 05:13

You need to store your identity value into a variable between the two inserts: and then use that variable in your second Insert call:

INSERT INTO dbo.InventoryTransfer (CFromSite, CToSite, CStatus, CUsername)
VALUES ('Store', 'Warehouse', 'Active', 'Chris');

DECLARE @IdValue INT = SCOPE_IDENTITY();

INSERT INTO dbo.InventoryTransferDetails (CTransferID, CQuantity, CBarcode) 
VALUES (@IdValue, 3, '734987598345');

How to use autogenerated ID from first INSERT in a second INSERT

1 Answers1