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.
Best Answer
You can either have the newly inserted ID being output to the SSMS console like this:
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 resultingID
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: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.