MS SQL Server Use insert into a table with select while output to third table

Question

So, here is the conundrum I am trying to solve, let us say I have the following tables, scripted as create statements

create table table1 (pk int identity(1,1) primary key, a int, b int)
create table table2 (pk int identity(100,1) primary key, a int, b int)
create table tbllink (tbl1pk int, tbl2pk int)

now, due to some requirements oversight, I now find myself in a situation where I need to take some of the records from table1, insert them into table2 after performing some kind of ad hoc calculation, and I need to be able to link them back to each other via the bridge tbllink.

This is fairly easy to do with a cursor, see below for cursor pseudo

cursor for select pk, a, b from table1 where (some clause)
fetch cursor into @pk, @a, @b
while @@fetch_status
begin
insert into table2 (a, b)
    output @pk, inserted.pk into tbllink
values (@a, @b + 5)
fetch cursor into @pk, @a, @b
end

This works and gets me exactly the results I need, but what I would really like to be able to do is something like this.

insert into table2 (a, b)
   output table1.pk, inserted.pk into tbllink
select a, b + 5 from table1 where (some clause)

this doesn't compile, though. Is there any way to accomplish this, or should I just use the cursor? Thanks.

Nothing wrong with a cursor for a one-off job. I'd only worry about avoiding the cursor if you were going to build it into the system for all new inserts going forward. — Joel Coehoorn
– Joel Coehoorn, Commented Aug 18, 2015 at 18:14
You have it close. MERGE into tbl1 output into temp table, insert into table2 with additional calcs, then link in tbllink. a regular insert won't return the newly created identity — Brad D
– Brad D, Commented Aug 18, 2015 at 18:33

AHiggins · Accepted Answer · 2015-08-18 18:37:46Z

2

There's a way to use the MERGE statement to do this:

-- Setup
    create table #table1 (pk int identity(1,1) primary key, a int, b int)
    create table #table2 (pk int identity(100,1) primary key, a int, b int)
    create table #tbllink (tbl1pk int, tbl2pk int)
    INSERT INTO #table1 (a,b) VALUES (1,2)

-- Load
    MERGE INTO #Table2 t USING (SELECT pk, a, b from #Table1) s ON 1 = 0
    WHEN NOT MATCHED THEN
    INSERT 
      (
        a,
        b
      )
    VALUES 
      (
        a,
        b
      )
    OUTPUT s.PK, inserted.PK
    INTO #TblLink (Tbl1pk, Tbl2pk);

-- Verify
    SELECT * FROM #table1
    SELECT * FROM #table2
    SELECT * FROM #tbllink

-- Clean up
    DROP TABLE #table1
    DROP TABLE #table2
    DROP TABLE #tbllink

answered Aug 18, 2015 at 18:37

AHiggins

7,2357 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3657661 Over a year ago

Thanks, this was very helpful

Collectives™ on Stack Overflow

MS SQL Server Use insert into a table with select while output to third table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related