September 12, 2005 at 10:29 am
On SQL version 8.00.760
I've got a DTS package that copies a subset of about 30 tables from a database on a "central" SQL server to the same database on a number of remote servers. Within the 30 tables being copied via the "Copy objects and data between SQL server databases" option there are a number of foreign key relationships setup. I have not been able to get past error messages stating that various tables could not be copied because of an existing foreign key relationship between various tables.
Is there a different methodology and/or advanced options that would work with these relationships and still copy the data.
In general, is this the best approach or is there a better way to copy data between SQL servers running the same database? Thanks.
September 12, 2005 at 10:51 am
Do the tables already exist in the destination databases? The trick is to copy the tables in the correct order i.e. the referenced tables, then the referencing tables. I've had the best luck scripting "drop constraints" and "create constraints" and executing those in Execute SQL tasks in a package with Transform Data tasks actually moving the data rather than using the Copy Objects task.
Greg
Greg
September 12, 2005 at 11:05 am
Thanks, and yes the tables already exist in the destination database. I was thinking along the same lines that the order might be important but have to admit that I'm not sure what you mean about "drop constraints" and "create constraints" Would creating two packages, one for referenced tables and one for referencing tables work?
September 12, 2005 at 11:30 am
Two packages aren't necessary. We run a script in Query Analyzer to generate ALTER TABLE DROP CONTRAINT and ALTER TABLE ADD CONSTRAINT statements, in the correct order, for a database, then copy the statements into Execute SQL tasks in the DTS package. The DROP statements go in an Execute SQL task before the Transform Data task, the ADD statements go in another Execute SQL task after the Transform Data task.
So the order of execution within the package is:
1. drop FK constraints
2. import data
3. add FK constraints
I posted the FK script previously in this thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=144674
Greg
Greg
September 12, 2005 at 11:38 am
Thanks. I understand the concept, I just need to get my mind around the scripting.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply