Post date: Apr 28, 2011 5:47:00 PM
Scenario:
I have an inheritance design and i want to insert data to my parent table and child table at the same time. The child table need to be inserted with the IDENTITY value from the parent table and use that as a foreign key to the parent table.
Basic tables, using a persisted computed column in the child tables to make sure that they are truly of the right "type"
CREATE TABLE [dbo].[ItemType]( [ItemTypeID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ItemType] [varchar](50) NOT NULL,)INSERT ItemType VALUES ('Product'), ('Article'), ('Service')CREATE TABLE [dbo].[Item]( [ItemID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ItemTypeID] [int] NOT NULL, [Name] [varchar](500) NOT NULL, [Created] [date] NOT NULL, UNIQUE([ItemID], [ItemTypeID]) ,FOREIGN KEY ([ItemTypeID]) REFERENCES [dbo].[ItemType]([ItemTypeID]))CREATE TABLE [dbo].[Product]( [ProductID] [int] NOT NULL primary key, [ItemTypeID] AS ((1)) PERSISTED NOT NULL, [Cost] [decimal](10, 2) NOT NULL, FOREIGN KEY([ProductID], [ItemTypeID]) REFERENCES [dbo].[Item] ([ItemID], [ItemTypeID]) )
Now, the easy way would be to just insert the Item(Parent table) and then get the Identity using scope_identity() and then insert the Product(child) using that value as ProductID:
declare @id intInsert Item(ItemTypeID, Name, Created)VALUES(1, 'Lemon ice cream', GETDATE())select @id = SCOPE_IDENTITY()insert Product(ProductID, Cost)values (@id, 25)
easy but boring, and you have to declare that local variable, meh.
Second solution.
For the insert statement there is an OUTPUT clause that returns the row(s) that was inserted using the INSERTED table.
For example:
INSERT Item(,,,)OUTPUT INSERTED.*VALUES (,,,)
That would return all the columns in the new order row. You can have a target table in the output clause to insert your INSERTED row into another table. However, you cannot use the OUTPUT clause to insert into any table that have a foreign key relation to the inserted table(Item in this example). That is restricted and would give an error.
However!
If you use dynamic SQL to insert to the parent table, and returning the culumns wanted using the OUTPUT clause and the INSERTED table, then you can insert the row into the child table in the same query.
Cool stuff.
Insert into Product exec sp_executesql N'insert Item(ItemTypeID, Name, Created) output INSERTED.ItemID as ProductID, 25 as cost values(1, db_name(), GETDATE())'
Kinda tricky to read.
The VALUES(,,) are for the parent table.
The columns specified in the output clause will be the values to insert into the Child table.
The resulting Product will have(including information from the parent table)