Return all Ids generated by Insert statement

Question

I have am passing a table valued parameter to an SP which will then insert data into two tables. The second tables need Id (Primary Key) of the first table. The structure is something like below:

Table_1
(
  Id BIGINT NOT NULL , --Primary Key
  FirstName NVARCHAR(50),
  LastName NVARCHAR(50),
  Category NVARCHAR(10),
  UniqueIdentifier NVARCHAR(100)
)

Table_2
(
  Id BIGINT NOT NULL, --Primary Key
  UserId BIGINT REFERENCES Table_1(Id) NOT NULL,
  Role NVARCHAR(20)
)

Now, I wish to insert data into table 1 as

INSERT INTO Table_1
(FirstName, LastName, Category, UniqueIdentifier)
SELECT tmp.FN, tmp.LN, tmp.Cat, tmp.UniqueIdentifier
FROM #TempTable tmp;

and into table 2 as

INSERT INTO Table_2
(UserId, Role)
SELECT out.Id, tmp.Role
FROM #TempTable2 tmp
JOIN OUTPUT out
ON tmp.UniqueIdentifier = out.UniqueIdentifier

here I wish to fill OUTPUT as Id and UniqueIdentifier from the result of the first insert. I know this can be achieved for single record insert using OUTPUT.Id and OUTPUT.UniqueIdentifier. But how do I achieve a similar functionality for this particular case?

Answer 1

Try:

-- create temp table to hold the id and unique ids
CREATE TABLE #table1_ids(id bigint not null, 
                         [UniqueIdentifier] NVARCHAR(100));

-- insert data into 1st table and obtain the ids into above temp ids table 
INSERT INTO Table_1(FirstName, LastName, Category, [UniqueIdentifier])
OUTPUT inserted.ID, inserted.[UniqueIdentifier] INTO #table1_ids 
SELECT tmp.FN, tmp.LN, tmp.Cat, tmp.UniqueIdentifier
FROM #TempTable tmp;

-- insert data into 2nd table using the above temp ids table
INSERT INTO Table_2(UserId, Role)
SELECT tids.Id, tmp.Role
FROM #TempTable2 tmp
INNER JOIN #table1_ids tids ON 
     tmp.UniqueIdentifier = tids.UniqueIdentifier