How do I use an INSERT statement's OUTPUT clau

问题:

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.

回答1:

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.