User Defined Table and Variable

Question

I have a stored procedure that inserts multiple rows into a table via a USER defined tyble. The user defined type is populated from a c# datatable.

Within the stored procedure, before the insert command with the datatable executes, I have another insert command into another table. Scope_Identity() to grab the ID of the newly inserted row and assign it to a variable.

What I would like to do is add that ID to all the rows that are added with the User defined type.

What's the best way to do this? How can I incorporate the Scope_Identity value into the insert statement?

If I'm understanding correctly, you want one id assigned to all your other records. If so you can store Scope_Identity() in a variable and then do something like this. Insert into tableName select @IdentityVariable,(rows from data set) — ZeRaTuL_jF
And what is the problem? You did create table type, managed to fill it in app, managed to grab scope identity, and you don't know how to combine table valued parmeter set with one variable???? — Giorgi Nakeuri

sqluser sqluser · Accepted Answer · 2015-04-21T01:12:33

If I am not mistaken, you can set the SCOPE_IDENTITY to an integer variable and use it in your INSERT

DECLARE @lastId INT

-- Your first INSERT
INSERT INTO yourTable1 ....

SET @lastId = SCOPE_IDENTITY()

-- Your second INSERT
INSERT INTO yourTable2(...)
VALUES(@lastId, [user data types], ...)

User Defined Table and Variable

1 Answers