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...
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 Answer
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
