This question already has answers here:

How to get the identity of an inserted row? (15 answers)
Closed 17 days ago.

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.