7

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
Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
moe
  • 5,149
  • 38
  • 130
  • 197

2 Answers2

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

insert with output

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
-2
 INSERT INTO school_year_studentid 
             (student_id,syrid) 
      VALUES (
               (
                SELECT student_id 
                  FROM student
                ), (
                     SELECT syr_id 
                       FROM school_year
                   )
                )
Prabhat Sinha
  • 1,500
  • 20
  • 32
John Nico Novero
  • 539
  • 5
  • 10