insert into several inheritance tables with OUTPUT - sql servr 2005
- by csetzkorn
Hi,
I have a bunch of items – for simplicity reasons – a flat table with unique names seeded via bulk insert:
create table #items
(
ItemName NVARCHAR(255)
)
The database has this structure:
create table Statements (
Id INT IDENTITY NOT NULL,
Version INT not null,
FurtherDetails varchar(max) null,
ProposalDateTime DATETIME null,
UpdateDateTime DATETIME null,
ProposerFk INT null,
UpdaterFk INT null,
primary key (Id)
)
create table Item (
StatementFk INT not null,
ItemName NVARCHAR(255) null,
primary key (StatementFk)
)
Here Item is a child of Statement (inheritance). I would like to insert items in #items using a set based approach (avoiding triggers and loops). Can this be achieved with OUTPUT in my scenario.
A ‘loop based’ approach is just too slow where I use something like this:
insert into Statements (Version, FurtherDetails, ProposalDateTime, UpdateDateTime, ProposerFk, UpdaterFk) VALUES (1, null, getdate(), getdate(), @user_id, @user_id)
etc.
This is a start for the OUTPUT based approach – but I am not sure whether this would work in my case as ItemName is only inserted into Item:
insert into Statements (
Version, FurtherDetails, ProposalDateTime, UpdateDateTime, ProposerFk, UpdaterFk
)
output inserted.Id ... ???
Thanks.
Best wishes,
Christian
