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
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.