Insert multiple values into multiple tables using a single statement in SQL Server

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

My Personal Notes arrow_drop_up

Save