0

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

1
  • Why dont you define PK FK relationship,adding only one extra column,so that it is clear to developer across generation.Why r you going the other way ?
    – KumarHarsh
    Commented Dec 8, 2023 at 9:58

1 Answer 1

2

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 Richie Rump has a blog post on it here.

5
  • Don't forget your isolation level. Commented Nov 22, 2023 at 16:51
  • 1
    There's no "match" phase in this MERGE, which is what requires U/X-Range locking in the normal case. It's really just a fancy INSERT. Commented Nov 22, 2023 at 17:09
  • @ErikDarling Thank you for the article! Upon reading it, I understand that concurrent issues can usually happen when there's an update involved rather than an insert. Since, in this case, I'm only inserting new ids, will there be any issues with concurrency? I can' think of anything but would really appreciate any insight. Thank you! Commented Nov 22, 2023 at 17:12
  • 3
    @lifeisajourney no, it’s not necessary for just an insert, but when you or someone else copies and pastes this code a few months or years from now to use for something else, you’ll either want it there, or a comment about why you don’t have it here. Commented Nov 22, 2023 at 18:38
  • 2
    The source side's subquery seems unnecessary, although I'd recommend it if there was any filtering on the source (rather than putting it in the ON). Commented Nov 23, 2023 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.