sql-servert-sqlstored-proceduresuser-defined-data-types

Multiple inserts from same table, but 2nd insert needs identity from 1st insert


This is a contrived example to illustrate the issue :

I have a stored procedure that needs to insert in these two tables

  1. Person(ID_Person int identity, Forname varchar)
  2. PersonExtension(ID_Person int, Age int)

It takes a user-defined table type as a parameter, so I can pass a list of people

create type ListOfPeople as table
(
    Forname varchar(20),
    Age int
)

I want to insert into Person and PersonExtension, but the second insert needs the identity column of the first

create procedure MyProcedure
    @ListOfPeople ListOfPeople readonly
as
begin
    insert into Person(Forname)
    select Forname from @ListOfPeople

    insert into PersonExtension(ID_Person, Age)
    select ?, Age from @ListOfPeople
end

How to do this efficiently ?


Solution

  • I'm going to assume that the table type parameter you have Can have multiple rows, due to lack of evidence to suggest it wouldn't, and that if it would only have 1 row then scalar parameters would be a much better choice.

    Firstly, we need to change the definition of your table type parameter, as we need the rows to have an ID of some kind:

    CREATE TYPE dbo.ListOfPeople AS table (ID int IDENTITY, --defaults to 1,1
                                           Forename varchar(20), --Forename has an e in it
                                           Age int); --Storing the age of something is a really bad idea. Store their DoB
    

    Now what we need to do is OUTPUT the value of the IDENTITY value that was created in the INSERT along with the value of the ID from your table type parameter, and insert that into a table variable. In T-SQL, you can't OUTPUT columns that aren't part of the INSERT. You can, however, do so with a MERGE. Then we can use the values insert to perform a JOIN and get the values:

    CREATE PROC dbo.MyProcedure @ListOfPeople dbo.ListOfPeople READONLY AS
    BEGIN
    
        DECLARE @Persons table (ID int, PID int);
    
        MERGE INTO Person USING @ListOfPeople AS LOP ON 1 = 0
        WHEN NOT MATCHED THEN
            INSERT (forename)
            VALUES (LOP.forename)
            OUTPUT LOP.ID, inserted.ID --Assumed Person's ID column is ID
            INTO @Persons (ID, PID);
    
        INSERT INTO dbo.PersonExtension(ID_Person,Age) --Again, storing Age is a bad idea
        SELECT P.PID,
               LOP.Age
        FROM @Persons P
             JOIN @ListOfPeople LOP ON P.ID = LOP.ID;
    
    END;