Get SCOPE_IDENTITY value when inserting bulk records for SQL TableType

Question

I have following table structure, for convenience purpose I am only marking individual columns

• Table_A (Id, Name, Desc)
• Table_1 (Id this is identity column, Name....)
• Table_2 (Id this is 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 column and from my C# app I send multiple records). I have achieved this bulk insert functionality as desired.

What I need is when I insert records into Table_2 from TType_Table_A say with below statements, I would like to capture the Id of Table_2 for each record inserted

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 say if 2 records are inserted, I would like to capture the Id for each of these 2 records.

Any input/help is appreciated

This is what I know how it can be achieved, but I want to reduce DB calls from my app or user cursor in stored procedure

Insert record in Table_1 and return back the Id Loop.....through records and insert record in Table_2 and return back the Id

OR

Use cursor in stored procedure when inserting/selecting from TableType

Pythia

Answer 1

I assume this is Sql Server? Then you can make use of the OUTPUT clause, like so:

declare @NewId table (MyNewId INT) 

insert into Table_2(Table_A_Id,Table_1_Id)
output inserted.MyNewId INTO @NewId (MyNewID)
SELECT SomeValue, SomeValueAsParameter FROM @TType_Table_A;

SELECT * FROM @NewId

Racing