Digiguru.co.uk

How to Insert Multiple rows into Multiple tables in SQL server

15 Apr 2011

Reading time: 2 minutes

I had a very bizarre need to insert multiple rows into multiple tables into SQL Server, let me show you a little picture. The concept is a seating plan, imagine a circular table that has 12 possible seats around it. I want to be able to say to the database "Create a table with 12 seats around it". To do this, I would need to insert 1 row into the table called "Table", add 12 rows to "Seat", and then insert the same 12 SeatIDs into "PossibleSeat" table. I thought this could be done with a simple CTE, but I couldn't figure out how to create all the rows into PossibleSeat and get all the IDs out without re-selecting from the table or by using a loop of some kind. Then I discovered the "Output With" clause.
An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.
So firstly i'll use Scope_Identity  to insert into our "Table" table, and store the ID
DECLARE @Table TABLE ( id          INT IDENTITY (1, 1), name        VARCHAR(50), datecreated DATETIME DEFAULT Getdate())
DECLARE @TableID INT INSERT INTO @Table (name) SELECT 'Toms table'
SELECT @TableID = Scope_identity(); SELECT * FROM   @table
Next we use a CTE to recursivly create a row for each Seat
DECLARE @TableSize INT SET @TableSize = 50
WITH seatcounter ( n ) AS (SELECT 1 UNION ALL SELECT 1 + n FROM   seatcounter WHERE  n < @TableSize) SELECT  n FROM   seatcounter OPTION ( MAXRECURSION 32767 )
Finally how to use the Output With clause
DECLARE @PossibleSeat TABLE ( id          INT IDENTITY (101, 1), seatid      INT, datecreated DATETIME DEFAULT Getdate())
DECLARE @Seat TABLE ( id          INT IDENTITY (31, 1), guestid     INT, datecreated DATETIME DEFAULT Getdate())
DECLARE @TableSize INT SET @TableSize = 50
SELECT @TableID = Scope_identity(); WITH seatcounter ( n ) AS (SELECT 1 UNION ALL SELECT 1 + n FROM   seatcounter WHERE  n < @TableSize) INSERT INTO @Seat OUTPUT inserted.id AS seatid, Getdate() INTO @PossibleSeat SELECT NULL AS guestid, Getdate() FROM   seatcounter OPTION ( MAXRECURSION 32767 ) SELECT * FROM   @PossibleSeat SELECT * FROM   @Seat
And put it all together
DECLARE @Table TABLE ( id          INT IDENTITY (1, 1), name        VARCHAR(50), datecreated DATETIME DEFAULT Getdate())
DECLARE @PossibleSeat TABLE ( id          INT IDENTITY (101, 1), tableid     INT NOT NULL, seatid      INT, datecreated DATETIME DEFAULT Getdate())
DECLARE @Seat TABLE ( id          INT IDENTITY (31, 1), guestid     INT, datecreated DATETIME DEFAULT Getdate())
DECLARE @TableID INT DECLARE @TableSize INT SET @TableSize = 50 INSERT INTO @Table (name) SELECT 'Toms table'
SELECT @TableID = Scope_identity(); WITH seatcounter ( n ) AS (SELECT 1 UNION ALL SELECT 1 + n FROM   seatcounter WHERE  n < @TableSize) INSERT INTO @Seat OUTPUT      @TableID, inserted.id AS seatid, Getdate() INTO @PossibleSeat SELECT NULL AS guestid, Getdate() FROM   seatcounter OPTION ( MAXRECURSION 32767 ) SELECT * FROM   @table SELECT * FROM   @PossibleSeat SELECT * FROM   @Seat
Outputs....
id Name DateCreated
1	Toms table	2011-04-15 17:40:58.773

id tableID seatID DateCreated
101	1	31	2011-04-15 17:40:58.773
102	1	32	2011-04-15 17:40:58.773
103	1	33	2011-04-15 17:40:58.773
104	1	34	2011-04-15 17:40:58.773
105	1	35	2011-04-15 17:40:58.773
106	1	36	2011-04-15 17:40:58.773
107	1	37	2011-04-15 17:40:58.773
108	1	38	2011-04-15 17:40:58.773
109	1	39	2011-04-15 17:40:58.773
110	1	40	2011-04-15 17:40:58.773
111	1	41	2011-04-15 17:40:58.773
112	1	42	2011-04-15 17:40:58.773

id guestID DateCreated
31	NULL	2011-04-15 17:40:58.773
32	NULL	2011-04-15 17:40:58.773
33	NULL	2011-04-15 17:40:58.773
34	NULL	2011-04-15 17:40:58.773
35	NULL	2011-04-15 17:40:58.773
36	NULL	2011-04-15 17:40:58.773
37	NULL	2011-04-15 17:40:58.773
38	NULL	2011-04-15 17:40:58.773
39	NULL	2011-04-15 17:40:58.773
40	NULL	2011-04-15 17:40:58.773
41	NULL	2011-04-15 17:40:58.773
42	NULL	2011-04-15 17:40:58.773
In this article I showed how it's actually quite easy to get multiple IDs out of an insert statement, and use them in another insert statement, effectively inserting multiple rows into multiple tables at once.