How to INSERT into multiple tables from one SELECT statement

Question

I am trying to insert into multiple tables from one select statement. Is it possible?

Here is what I am trying to do here:

insert into table1 (Name, Address)
insert into table2 (Name, Address)
select Name, Address from MainTable

It is not possible with a single statement. But why would you want to do this? — Gordon Linoff, Jan 30 '16 at 21:08
@GordonLinoff, it **is** possible in a single statement in SQL Server 2008 - the `OUTPUT` clause can do it. (at least for two destinations tables). — Vladimir Baranov, Jan 31 '16 at 03:48

Vladimir Baranov · Accepted Answer · 2016-01-31T04:09:38.023

16

You can use the OUTPUT clause to insert into the second table. If you want to insert into more than two tables, you'd have to use some other method.

Sample data

DECLARE @MainTable TABLE (Name nvarchar(50), Address nvarchar(50));
DECLARE @T1 TABLE (Name nvarchar(50), Address nvarchar(50));
DECLARE @T2 TABLE (Name nvarchar(50), Address nvarchar(50));

INSERT INTO @MainTable (Name, Address) VALUES
('Name1', 'Address1'),
('Name2', 'Address2'),
('Name3', 'Address3');

Query

INSERT INTO @T1 (Name, Address)
OUTPUT inserted.Name, inserted.Address INTO @T2 (Name, Address)
SELECT Name, Address
FROM @MainTable
;

Result

SELECT * FROM @T1;
SELECT * FROM @T2;


+-------+----------+
| Name  | Address  |
+-------+----------+
| Name1 | Address1 |
| Name2 | Address2 |
| Name3 | Address3 |
+-------+----------+

+-------+----------+
| Name  | Address  |
+-------+----------+
| Name1 | Address1 |
| Name2 | Address2 |
| Name3 | Address3 |
+-------+----------+

Execution plan

edited Jan 31 '16 at 04:09

answered Jan 31 '16 at 03:46

Vladimir Baranov

31,799
5
53
90

1

+1 In general I like this (`OUTPUT` clause is really handy), but am still curious what the actual goal of the O.P. is. Seems like it might be simply to not need to run the query twice, but still. – Solomon Rutzky Jan 31 '16 at 04:01
@srutzky, yes, it would be good to know the actual problem. In this simple example execution plan is simple and doesn't even have any temporary tables - two INSERTs are chained together. For a complex SELECT query it may be different. – Vladimir Baranov Jan 31 '16 at 04:08
1

@VladimirBaranov . . . You are technically correct. I only use `OUTPUT` with table variables, so I forget it works with any table. Also, you can only insert values into the second table that are in the first table. – Gordon Linoff Jan 31 '16 at 17:37
3

@GordonLinoff using merge to do the insert would make any columns from the source table available in the output clause http://stackoverflow.com/q/5365629/73226 – Martin Smith Jan 31 '16 at 17:55

score -2 · Answer 2 · edited Jun 19 '18 at 11:00

-2

 INSERT INTO school_year_studentid 
             (student_id,syrid) 
      VALUES (
               (
                SELECT student_id 
                  FROM student
                ), (
                     SELECT syr_id 
                       FROM school_year
                   )
                )

edited Jun 19 '18 at 11:00

Prabhat Sinha

1,500
20
32

answered Jun 19 '18 at 08:43

John Nico Novero

539
5
10

How to INSERT into multiple tables from one SELECT statement

2 Answers2

Linked