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