insert data into several tables - possibly using OUTPUT - sql server 2005

Question

Dear all,

I would like to insert data from a file into the table Division of this model which is very much simplified:

create table Statements (
        Id INT IDENTITY NOT NULL
       primary key (Id)
    )

create table Item (
        StatementFk INT not null,
       ItemNameFk INT not null,
       primary key (StatementFk)
    )

create table ItemNames (
        Id INT not null,
       Name NVARCHAR(255) null unique,
       primary key (Id)
    )

create table Division (
        ItemFk INT not null,
       primary key (ItemFk)
    )

alter table Item 
        add constraint FKBCDC2F95A77158D3 
        foreign key (StatementFk) 
        references Statements

    alter table Item 
        add constraint FKBCDC2F957105B2A7 
        foreign key (ItemNameFk) 
        references ItemNames

alter table TaxonomyDivision 
        add constraint FKCFB817265F647111 
        foreign key (ItemFk) 
        references Item

Here Division is a child object of Item and Item is a child object of Statements. The original data looks like this:

Division1
Division2
Division1
Division3

The result should be:

ItemNames
1, Division1
2, Division2
3, Division3

Statements
1
2
3
4

Item
1,1
2,2
3,1
4,3

Division
1
2
3
4

I think the way forward is to use OUTPUT but I don’t know how to use it in this fairly complicated case (I know how to do a bulk insert etc.)

Looking forward to any feedback. Thanks.

Best wishes,

Christian

Answer 1

Hi,

I am currently using loops to achieve this which is kind of a solution but very slow. Is there really no set based approach? Thanks!

Christian