Get SCOPE_IDENTITY value when inserting massive records for SQL TableType
I have the following table structure, for convenience I only mark individual columns
-
Table_A
(Id, Name, Desc
) -
Table_1
(Id
this is the identity column,Name
....) -
Table_2
(Id
This identity columnTable_A_Id
,Table_1_Id
)
The relationship between Table_1 and Table_2 is 1...*
Now I have created a table type for Table_A
called TType_Table_A
(which only contains Id
as a column, and from my C # app I am sending multiple records). I have achieved this functionality of a capacious insert as desired.
I need, when I insert records in Table_2
from TType_Table_A
, say with the instructions below, I would like to record Id
from Table_2
for each inserted record
declare @count int = (select count(*) from @TType_Table_A); --a variable declared for TType_Table_A
if(@count > 0)
begin
insert into Table_2(Table_A_Id,Table_1_Id)
SELECT @SomeValue, @SomeValueAsParameter FROM @TType_Table_A;
end;
Now tell me if there are 2 records inserted, I would like to capture Id
for each of these two records.
Any input / help is greatly appreciated
This is what I know how this can be achieved, but I want to reduce DB calls from my application or user cursor in a stored procedure
Insert record into table_1 and return the outline Id ..... through records and insert record into table_2 and return the id
OR
Use cursor in stored procedure when inserting / selecting from TableType
source to share
I am assuming this is Sql Server? Then you can use the OUTPUT clause like:
declare @NewId table (MyNewId INT)
insert into Table_2(Table_A_Id,Table_1_Id)
output INSERTED.MyNewId INTO @TempTable(MyNewID)
SELECT SomeValue, SomeValueAsParameter FROM @TType_Table_A;
SELECT * FROM @NewId
source to share