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.773In 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.