Is it possible to make an Update query right after Insert returned new Id's

Question

I am using MS Sql Server 2008R2.

For example I need to insert many rows inside TableA and I want to make an Update another TableB with newly inserted Ids from TableA.

Is it possible to do in one SQL query?

Answer 1

OUTPUT Clause

DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason (Name, ModifiedDate)  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

you can then use table for other inserts / updates

Answer 2

Is it possible to do in one SQL query?

No. But it is possible to make it in one transaction. Just handle the transaction on the client side properly.

You can also send both statements in one run (called batch). But they will still be separate statements. And both are not queries - per definition, an update statement is not a query.

Answer 3

If you need this to happen every time regardless of where the INSERT comes from you could use a Trigger on the table which can be set to run on INSERT, UPDATE or DELETE or any combination of the above.