1

I have SQL TVP object with multiple records (for example 2 records). I need to insert these records into two almost identical tables, the only difference is that second table has one more column which is foreign key pointing to first table. So it should loop TVP records and insert one by one into both tables, but getting scope_identity() of inserted record in first table and use it for record in second table.

1st iteration

  • insert into first table
  • get scope_identity() of inserted record
  • insert into second table (using scope indentity from first table to fill additional column)

And so on, depending on how many records are in TVP.

How can I achieve this?

2 Answers2

0

Obviously I have left out a ton of code since we don't have your column and table names etc. You want an ID value in your TVP so you can count rows and use it in a where clause and while loop.

Declare @Var1 Int
Declare @YourTVP YourTVPName
Declare @RowCounter Int = 1


While (1=1)

Insert Into YourTable1 (Column1, ...)
Select (Column1, ...) 
From @YourTVP
Where @RowCounter = SomeIDColumn
@Var1 = Select @@SCOPE_IDENTITY()


Insert Into YourTable2 (Column1, ...)
(@Var1, ...)

If (Some logic to Break your While loop)
Break
Else @RowCounter = @RowCounter + 1
End
Jacob H
  • 2,455
  • 1
  • 12
  • 29
0

Ok, let me be more clear. I will give demonstrative example::

  • I have TVP (let name it as PersonTVP) containing FirstName and LastName columns and assume PersonTVP has two records.

  • I have two tables, Person and PersonExtra. Person table has Id, FirstName and LastName columns, and PersonExtra has same columns + one additional column PersonId.

I need to insert data from PersonTVP into these two tables. Flow should be:

  • Take record from PersonTVP and insert into Person table
  • Get Scope_Identity() of inserted record (the value from Id column)
  • Insert same record into PersonExtra table and use Scope_Identity() for PersonId column (additional column)

And so on, loop as long as PersonTVP has records.