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.
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.
real persistent table - this is extremely fantastic because it means that you can INSERT information in TWO tables at the same time.
SCOPE_IDENTITY() works better for that.I didn't like the 'trust me, it's the last ID somewhere' element of the above, so browsed Learn.Microsoft and with a bit of jiggling got a neat and exact solution, informed by Micrsosoft examples. (You might not like the old-man-style code, but hey, whatever). It's a C# function but you can extract the SQL elements as required.
const string queryString = "DECLARE @MyTableVar TABLE(NewParaID INT); " + "INSERT into dbo.Para(ParaText) " + "OUTPUT INSERTED.ParaID INTO @MyTableVar " + "VALUES(@para); " + "SELECT NewParaID FROM @MyTableVar; "; using (SqlConnection connection = new(m_sConnectionString)) { SqlCommand command = new(queryString, connection); command.Parameters.AddWithValue("@para", sPara); try { connection.Open(); nParaID = (int)command.ExecuteScalar(); } catch (Exception ex) { sError = ex.Message; bResult = false; } } 
OUTPUTclause.OUTPUTclause writes to a table. It can be a table variable, temporary table, ... .