INSERT Data From One Table Into Multiple Tables

Question

I'm using SQL Server 2005.

I am migrating data over from a current database (single table) to a new database (normalized - many tables). In the new database, I have a base table (let's call it "BaseTable"), and multiple other tables (let's call them "DependentA", and "DependentB"). Some of the data from the old database will go to BaseTable, and some will go to the other two. BaseTable has a one-to-one relationship with both DependentA and DependentB, using the Id of them as the foreign key.

So here's my question. How should I migrate the data over? Here is a query I've been trying, which is working except for one thing: the foreign keys in BaseTable for the other two are identical, instead or having a different one each.

Begin SQL:

BEGIN TRANSACTION

DECLARE @dep1Id int

DECLARE @dep2Id int

INSERT INTO DependentA (column1, column2)
SELECT c1, c2
FROM OldDatabase.OldTable
SELECT @dep1Id = Scope_Identity()

INSERT INTO DependentB (column3, column4)
SELECT c3, c4
FROM OldDatabase.OldTable
SELECT @dep2Id = Scope_Identity()

INSERT INTO BaseTable (column5, dependentTable1Id, dependentTablr2Id)
SELECT c5, @dep1Id, @dep2Id
FROM OldDatabase.OldTable

COMMIT

Do any of the columns in the dependent tables hold unique values that could be looked-up? (i.e. c1 & c3) — CAbbott
– CAbbott, Commented Feb 10, 2010 at 23:02
There is a primary key in those tables (DependentA and DependentB). — jchapa
– jchapa, Commented Feb 10, 2010 at 23:05

Paul Creasey · Accepted Answer · 2010-02-10 23:04:29Z

7

The problem is that @dep1Id and @dep1Id are scalar and are retaining the last value only from the two set based inserts.

Since it's a one off you should probably do it as a cursor

DECLARE CURSOR @curs FOR
SELECT c1,c2,c3,c4,c5 FROM OldDatebase

open @curs
fetch next from @curs into
@c1,@c2,@c3,@c4,@c5 --declare these!

while @@fetch_status <> 0
BEGIN

INSERT INTO DependentA (column1, column2) VALUES @c1, @c2

SELECT @dep1Id = Scope_Identity()

INSERT INTO DependentB (column3, column4) VALUES @c3, @c4 

SELECT @dep2Id = Scope_Identity()

INSERT INTO BaseTable (column5, department1Id, department2Id) @c5, @dep1Id, @dep2Id    

fetch next from @curs into
@c1,@c2,@c3,@c4,@c5
END
close @curs
deallocate @curs

My cursor syntax is probably riddled with errors, but you get the idea.

answered Feb 10, 2010 at 23:04

Paul Creasey

28.9k10 gold badges60 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shahdat Over a year ago

cursor is not feasible for large data set even worst for me with 261000 rows

Dan · Accepted Answer · 2012-03-15 15:37:05Z

4

To avoid a cursor for large data sets, temporarily include the OldTable_id in the new tables.

BEGIN TRANSACTION

INSERT INTO DependentA (OldTable_id, column1, column2)
SELECT ot.id, ot.c1, ot.c2
FROM OldDatabase.OldTable ot

INSERT INTO BaseTable (OldTable_id, column5)
SELECT ot.id, ot.c5
FROM OldDatabase.OldTable ot

UPDATE BaseTable 
    SET BaseTable.dependentTable1_id = DependentA.id
    FROM BaseTable
    INNER JOIN DependentA on DependentA.OldTable_id = BaseTable.OldTable_id

COMMIT

Do the same for DependentB table and any other tables being normalized out of the OldTable.

Delete OldTable_id after the data migration.

answered Mar 15, 2012 at 15:37

Dan

62410 silver badges16 bronze badges

Comments

TT. · Accepted Answer · 2017-10-24 07:12:51Z

1

[enter image description here][1]ZeorOne is the main table from which you want to get data and insert it into zero and one table respectively.

select idzero,namezero,idone,nameone from zeroone

insert into zero 
select idzero,namezero from zeroone

insert into one
select idone,nameone from zeroone

or you want to use cursor to insert data with selected columns from Zeroone into to two tables the query is here

Declare @idzero int
Declare @namezero varchar(50)
Declare @idone int
Declare @nameone varchar(50)

Declare Cur Cursor  for
select idzero,namezero,idone,nameone from zeroone

open Cur

fetch Cur into @idzero,@namezero,@idone,@nameone

While @@fetch_status = 0
begin 

    insert into zero 
    select @idzero,@namezero 

    insert into one
    select @idone,@nameone 

    fetch Cur into @idzero,@namezero,@idone,@nameone

end 

close Cur
Deallocate Cur

edited Oct 24, 2017 at 7:12

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered Oct 24, 2017 at 6:25

Muhammad Farooq

112 bronze badges

1 Comment

Mathieu VIALES Over a year ago

I'm not sure that this answer, to a 7 years old post adds much value to the thread ...

AligNoor · Accepted Answer · 2020-10-08 12:23:04Z

DECLARE @Product_Name varchar(50),@Generic_Name varchar(50),@Category_Name varchar(50),@Manufacture_Name varchar(50),
@UOM_Name varchar(50),@ProductId int,@GenericId int,@CategoryId int,@ManufactureId int,@UOMId int

DECLARE MultiplTable CURSOR FOR
SELECT ProductName,GenericName,CategoryName,ManufacturerName,UOMName from Noor_ProductList

open MultiplTable
fetch next from MultiplTable into
@Product_Name,@Generic_Name,@Category_Name,@Manufacture_Name,@UOM_Name --declare these!

while @@fetch_status = 0
BEGIN
INSERT INTO Noor_GenericMaster(GenericName) VALUES (@Generic_Name) 
SELECT @GenericId = Scope_Identity()

INSERT INTO Noor_CategoryMaster(CategoryName) VALUES (@Category_Name) 
SELECT @CategoryId = Scope_Identity()

INSERT INTO Noor_ManufaturerMaster(ManufaturerName) VALUES (@Manufacture_Name) 
SELECT @ManufactureId = Scope_Identity()

INSERT INTO Noor_UOMMaster(UOMName) VALUES (@UOM_Name)
SELECT @UOMId = Scope_Identity()

INSERT INTO Noor_ProductMaster (ProductName,GenericID,CategoryID,ManufaturerID,UOMID)
values (@Product_Name,@GenericId,@CategoryId,@ManufactureId,@UOMId)
SELECT @ProductId = Scope_Identity()  

fetch next from MultiplTable into @Product_Name,@Generic_Name,@Category_Name,@Manufacture_Name,@UOM_Name

END
close MultiplTable
deallocate MultiplTable

Collectives™ on Stack Overflow

INSERT Data From One Table Into Multiple Tables

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related