insert data into several tables

Question

Let us say I have a table (everything is very much simplified):

create table OriginalData (
ItemName NVARCHAR(255) not null
    )

And I would like to insert its data (set based!) into two tables which model inheritance

create table Statements (
        Id int IDENTITY NOT NULL,
ProposalDateTime DATETIME null
    )

create table Items (
        StatementFk INT not null,
       ItemName NVARCHAR(255) null,
       primary key (StatementFk)
    )

Statements is the parent table and Items is the child table. I have no problem doing this with one row which involves the use of IDENT_CURRENT but I have no idea how to do this set based (i.e. enter several rows into both tables).

Thanks.

Best wishes,

Christian

Answer 1

You will need to write an ETL process to do this. You may want to look into SSIS.

This also can be done with t-sql and possibly temp tables. You may need to store unique key from OriginalTable in Statements table and then when you are inserting Items - join OriginalTable with Statements on that unique key to get the ID.

Answer 2

I don't think you could do it in one chunk but you could certainly do it with a cursor loop

DECLARE @bla char(10)
DECLARE @ID int

DECLARE c1 CURSOR 
FOR
SELECT bla 
FROM OriginalData

OPEN c1

FETCH NEXT FROM c1
INTO @bla

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO Statements(ProposalDateTime) VALUES('SomeDate')
        SET @ID = SCOPE_IDENTITY()
        INSERT INTO Items(StateMentFK,ItemNAme) VALUES(@ID,@bla)
    FETCH NEXT FROM c1
    INTO @bla

END

CLOSE c1
DEALLOCATE c1

Answer 3

Another possible method that would prevent the use of cursors, which is generally not a best practice for SQL, is listed below... It uses the OUTPUT clause to capture the insert results from the one table to be used in the insert to the second table.

Note this example makes one assumption in the fact that I moved your IDENTITY column to the Items table. I believe that would be acceptable, atleast based on your original table layout, since the primary key of that table is the StatementFK column.

Note this example code was tested via SQL 2005...

IF OBJECT_ID('tempdb..#OriginalData') IS NOT NULL
  DROP TABLE #OriginalData
IF OBJECT_ID('tempdb..#Statements') IS NOT NULL
  DROP TABLE #Statements
IF OBJECT_ID('tempdb..#Items') IS NOT NULL
  DROP TABLE #Items

create table #OriginalData
( ItemName NVARCHAR(255) not null ) 

create table #Statements 
( Id int NOT NULL, 
  ProposalDateTime DATETIME null ) 

create table #Items
( StatementFk INT IDENTITY not null, 
  ItemName NVARCHAR(255) null, 
  primary key (StatementFk) ) 

INSERT INTO #OriginalData
  ( ItemName  )
          SELECT  'Shirt'
UNION ALL SELECT  'Pants'
UNION ALL SELECT  'Socks'
UNION ALL SELECT  'Shoes'
UNION ALL SELECT  'Hat'

DECLARE @myTableVar table 
  ( StatementFk int, 
    ItemName    nvarchar(255) )

INSERT INTO #Items
  ( ItemName  )
OUTPUT INSERTED.StatementFk, INSERTED.ItemName
INTO  @myTableVar
SELECT  ItemName
FROM    #OriginalData

INSERT INTO #Statements
  ( ID, ProposalDateTime  )
SELECT
    StatementFK, getdate()
FROM  @myTableVar