This is a contrived example to illustrate the issue :
I have a stored procedure that needs to insert in these two tables
Person(ID_Person int identity, Forname varchar)
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 ?
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;