SQL Server – How to Insert Multiple Rows and Map Newly Created IDs

querysql server

I have the following tables

    -- Create the #product table
    CREATE TABLE #product 
    (
        id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
        name VARCHAR(100),
        createdDate DATE
    );
    
    -- Create the #tmpproducts table
    CREATE TABLE #tmpProducts
    (
        rownum INT PRIMARY KEY IDENTITY(1,1),
        name VARCHAR(100),
        createdDate DATE
    );

-- Insert data into #tmpSignings
INSERT INTO #tmpProducts (name, createdDate)
VALUES
    ('John Doe', '2023-11-22'),
    ('Jane Smith', '2023-11-23'),
    ('Alice Johnson', '2023-11-24'),
    ('Bob Williams', '2023-11-25'),
    ('Eva Brown', '2023-11-26'),
    ('Michael Davis', '2023-11-27'),
    ('Sophia Miller', '2023-11-28'),
    ('William Wilson', '2023-11-29'),
    ('Olivia Jones', '2023-11-30'),
    ('Daniel White', '2023-12-01');

I have 10 rows in #tmpProducts and my objective is to insert these rows into the #product table. For each row successfully inserted into the product table, I need to associate the newly generated ID back to the corresponding rownum in the #tmpProducts table. How can I accomplish this? Any help is greatly appreciated. Thank you!

Expected Output:

#tmpProducts:
rownum name createdDate 1

#product:
enter image description here

final result:
enter image description here

Best Answer

Like this:

merge into #product as tgt
using (select * from #tmpProducts) as src on 1=0
when not matched
then insert(name,createdDate) values (name,createdDate)
output src.rownum, inserted.id;

outputs

rownum      id
----------- ------------------------------------
1           25EAAED2-1021-464A-BC79-C8736266FE3F
2           BB4C926B-BDDB-49AE-8EA8-3300176D8661
3           75033546-4269-4E2E-80EA-517F08C85A96
4           B1898A0D-FB53-48C4-AB4A-60D4E4E3B790
5           05F40C5A-F16B-4C9C-BBF0-E6E720097E65
6           96502E38-A4AE-4D67-91A6-1EE6E12DDA0C
7           E86899B7-6EA2-48AE-8A72-43EBECF402F7
8           1839CEF5-95B9-4E01-A32F-9A422032C220
9           EBE0F061-A1E2-4345-9450-51EB142D8650
10          9AED82EE-A7F1-4C3B-AE70-400FD940246F

(10 rows affected)

You have to use MERGE instead of INSERT as INSERT only allows you to OUTPUT columns from the target table. This is the pattern Entity Framework uses for batch inserts, and Brent Ozar has a blog post on it here.

Related Question