sql-servertemp-tablestable-valued-parametersscope-identity

Get multiple scope_identity while inserting data with table-valued parameter


I am inserting multiple rows into Table1 using table-valued parameter. Now I want to insert Table1's scope identity to Table2 with some values. How do I achieve that?


Solution

  • You can use the Output Clause clause for that, lets look at a sample

    suppose your Table1 looks like this

    Table1(Table1_ID int identity, Name varchar(100), Email varchar(100), ...)
    

    Now lets insert and catch all new ID's and Names :

    declare @OutputTbl table (ID INT, Name varchar(100))
    
    insert into Table1(Name, Email)
    output inserted.Table1_ID, inserted.Name into @OutputTbl(ID, Name)
    VALUES ('john doe', 'john@somewhere.com'), 
           ('Anna', 'Anna@1com')
    
    select * from @OutputTbl
    

    the result in @OutputTbl will be

    ID  Name    
    --  --------    
    18  john doe    
    19  Anna    
    

    Now you can off course insert all rows from @OutputTbl into another table if you so desire

    insert into Table2 (Table1_ID, Name) 
    select ID, Name
    from   @OutputTbl