ChatGPT解决这个技术问题 Extra ChatGPT

How do I use an INSERT statement's OUTPUT clause to get the identity value?

If I have an insert statement such as:

INSERT INTO MyTable
(  
  Name,
  Address,
  PhoneNo
)
VALUES
(
  'Yatrix',
   '1234 Address Stuff',
   '1112223333'
)

How do I set @var INT to the new row's identity value (called Id) using the OUTPUT clause? I've seen samples of putting INSERTED.Name into table variables, for example, but I can't get it into a non-table variable.

I've tried OUPUT INSERTED.Id AS @var, SET @var = INSERTED.Id, but neither have worked.

I know about @@SCOPE_IDENTITY already, I specifically want to know how to do it with OUPUT. Thanks.
You need to insert it into a table variable then select from that. There is no syntax to assign directly to a scalar variable from the OUTPUT clause.
The OUTPUT clause has to output into a table or table variable..
The OUTPUT clause writes to a table. It can be a table variable, temporary table, ... .
My question specifically asks for the OUTPUT clause.

m
marc_s

You can either have the newly inserted ID being output to the SSMS console like this:

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar() (instead of .ExecuteNonQuery()) to read the resulting ID back.

Or if you need to capture the newly inserted ID inside T-SQL (e.g. for later further processing), you need to create a table variable:

DECLARE @OutputTbl TABLE (ID INT)

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

This way, you can put multiple values into @OutputTbl and do further processing on those. You could also use a "regular" temporary table (#temp) or even a "real" persistent table as your "output target" here.


The answer here for the code behind was concise. ExecuteScalar() FTW
You can insert the result in a real persistent table - this is extremely fantastic because it means that you can INSERT information in TWO tables at the same time.
Don't ever use @@IDENTITY to pull from the top. Foud out the hard way working with triggers and since they were recording history of changes made to one table and inserting into a new table at the same time @@IDENTITY started returning back values from the history table. hilarity ensues from there! Please use marc_s' solution. for the time being I have went with the @OutputTbl method, but I'm intrigued by the other options.
OUTPUT INTO is extremely fantastic except that "The target table of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship", which for me is about 99% of potential use cases. I assume this is because the OUTPUT clause can return data even when the transaction is rolled back, but it's a little annoying it is so hard to insert into data into related tables A and B in one shot.
@EricBishard SCOPE_IDENTITY() works better for that.