December 28, 2007 at 4:58 pm
We have an old VB6 app used by about 150 separate remote sites that all use their own local SQL2000 database. The structure in each database is identical and only the local client data is different. We are now rewriting this system so that all the data will be in a single centralized SQL2005 database.
My task is to migrate the data from all the databases into the new one. The databases will come to us as backups on CD and I plan to restore them on our server one by one and then use SSIS to migrate the data.
For about one third of the tables this will be a simple 'select from olddb.tab1 insert into newdb.tab1' but for the remaining two thirds I need to cater for identity columns in parent / child tables as these will have to change and I need to make sure that child records have the same ID as their parent record.
So my questions are...is SSIS the right way to do this and if so how do I manage the identity column issue.
Thanks
i.e. How do I make sure that in the example below tab2a picks up the new parentID of 12345?
olddb.tab2 (ID=1, Client = 123)
olddb.tab2a (ID = 3, ParentID = 1)
newdb.tab2(ID = 12345, Client = 123)
newdb.tab2(ID = 4567, ParentID = 12345)
December 29, 2007 at 1:28 pm
I’m not sure SSIS would be the most practical for that situation although I’m certain it could be done.
One way to handle the identity column is to replace it. To do that, remove the constraints on both tables, then create a new column in both tables and update it with a value that would make it unique across all databases to be imported. After that, replace the original ID and ParentID columns with the new ones and put the primary and foreign key constraints back in place.
Here's a script that might get the results you're looking for and be easier to maintain.
CREATE TABLE TAB2
(
ID INT IDENTITY (1,1),
CLIENT INT NOT NULL,
CONSTRAINT PK_TAB2 PRIMARY KEY(ID)
)
CREATE TABLE TAB2A
(
ID INT IDENTITY (1,1),
PARENTID INT NULL,
CONSTRAINT PK_TAB2A PRIMARY KEY(ID),
CONSTRAINT FK_TAB2A_TAB2 FOREIGN KEY(PARENTID) REFERENCES TAB2(ID)
)
INSERT TAB2(CLIENT) VALUES (900)
INSERT TAB2(CLIENT) VALUES (901)
INSERT TAB2A(PARENTID) VALUES (1)
INSERT TAB2A(PARENTID) VALUES (2)
INSERT TAB2A(PARENTID) VALUES (2)
INSERT TAB2A(PARENTID) VALUES (1)
INSERT TAB2A(PARENTID) VALUES (2)
-- Display the results before making the change
SELECT A.CLIENT AS TAB2_CLIENT,A.ID AS TAB2_ID, B.PARENTID as TAB2A_PARENTID, B.ID AS TAB2A_ID
FROM TAB2 A
JOIN TAB2A B ON A.ID = B.PARENTID
ORDER BY CLIENT
-- Step 1: Drop the foreign key constraint on TAB2A
ALTER TABLE TAB2A DROP CONSTRAINT FK_TAB2A_TAB2;
-- Step 2: Drop the primary key constraint on TAB2
ALTER TABLE TAB2 DROP CONSTRAINT PK_TAB2;
-- Step 3: Add a new column to TAB2 and TAB2A; same data type as identity
ALTER TABLE TAB2 ADD NEW_ID INT NULL;
ALTER TABLE TAB2A ADD NEW_PARENTID INT NULL;
-- Step 4: Update the new columns with a value that would make it unique.
-- Change the RANGE for each database imported.
DECLARE @RANGE INT
SET @RANGE = 10000
PRINT @RANGE
UPDATE TAB2 SET NEW_ID = ID + @RANGE
UPDATE TAB2A SET NEW_PARENTID = PARENTID + @RANGE
-- Step 5: Rename the New_ID and New_ParentID columns back to original names.
ALTER TABLE TAB2 DROP COLUMN ID
EXEC sp_rename 'tab2.new_ID', 'id', 'COLUMN'
ALTER TABLE TAB2A DROP COLUMN ParentID
EXEC sp_rename 'tab2a.new_ParentID', 'ParentID', 'COLUMN'
-- Step 6: Add the primary key constraint back to TAB2
ALTER TABLE TAB2 ALTER COLUMN ID INT NOT NULL
GO
ALTER TABLE TAB2 ADD CONSTRAINT PK_TAB2 PRIMARY KEY(ID)
-- Step 7: Add the foreign key constraint back to TAB2A
ALTER TABLE TAB2A WITH NOCHECK ADD CONSTRAINT FK_TAB2A_TAB2 FOREIGN KEY(ParentID) REFERENCES TAB2(ID)
-- Display the results after the change
SELECT A.CLIENT AS TAB2_CLIENT,A.ID AS TAB2_ID, B.PARENTID as TAB2A_PARENTID, B.ID AS TAB2A_ID
FROM TAB2 A
JOIN TAB2A B ON A.ID = B.PARENTID
ORDER BY CLIENT
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply