CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
moe
moe

Reputation: 5249

How to INSERT into multiple tables from one SELECT statement

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

Upvotes: 7

Views: 19893

Answers (2)

John Nico Novero
John Nico Novero

Reputation: 655

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

Upvotes: -2

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

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

Upvotes: 16

Related Questions