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 column Table_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)
    insert into Table_2(Table_A_Id,Table_1_Id)
    SELECT @SomeValue, @SomeValueAsParameter FROM @TType_Table_A;


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


Use cursor in stored procedure when inserting / selecting from TableType


source to share

1 answer

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;





All Articles