Insert multiple values into multiple tables using a single statement in SQL Server
Last Updated :
12 Feb, 2022
The T-SQL function OUTPUT, which was introduced in 2005, can be used to insert multiple values into multiple tables in a single statement. The output values of each row that was part of an INSERT, UPDATE or DELETE operation are returned by the OUTPUT clause.
Syntax:
INSERT INTO Table1 (Col1, Col2)
OUTPUT inserted.Col1, inserted.Col2
INTO Table2
VALUES()
GO
Example: In this example, we are creating 2 sample tables using the below queries:
CREATE TABLE GeekTable1 (Id1 INT, Name1 VARCHAR(200), City1 VARCHAR(200))
GO
CREATE TABLE GeekTable2 (Id2 INT, Name2 VARCHAR(200), City2 VARCHAR(200))
GO
Now, let us Insert values into two tables together:
INSERT INTO GeekTable1 (Id1, Name1, City1)
OUTPUT inserted.Id1, inserted.Name1, inserted.City1
INTO GeekTable2
VALUES(1,'Komal','Delhi'), (2, 'Khushi','Noida')
GO
Select data from both the tables:
SELECT * FROM GeekTable1 ;
GO
SELECT * FROM GeekTable2 ;
GO
Output: When we run the above query, we will see that there are two rows each in the table:
GeekTable1:
Id1 |
Name1 |
City1 |
1 |
Komal |
Delhi |
2 |
Khushi |
Noida |
GeekTable2:
Id2 |
Name2 |
City2 |
1 |
Komal |
Delhi |
2 |
Khushi |
Noida |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...