December 2, 2004 at 12:07 am
Hi all,
I have two versions of the same database on SQL server 2000. The old database has data which needs to be moved to the new database. The new database has many additional columns and some columns have changed names from the old database. I would like to use DTS to do this migration and execute the package thru a ColdFusion web front-end.
My problem is there are a lot of foreign key constraints in the destination database which prevent insertion of data in the secondary tables without inserting in the master tables. How can I disable all constraints and identity columns in the destination database and copy the stuff from the source database using DTS wizard or DTS designer?
Once this is done I will have to enable all constraints and identity columns back to the previous state. How do I go about this? I'm a beginner in using DTS designer.
ANY HELP IS WELCOME!!!
December 2, 2004 at 8:29 am
u can try to disable the constraints manually in the destination and carryout the DTS..that wud be simpler
thnks
December 2, 2004 at 9:58 am
Create a package in DTS Designer. We use this script, which is a variation of a script from this site, to list FK constraints and generate DROP CONSTRAINT and ADD CONSTRAINT statements for the database. Run the script in Query Analyzer and copy the resulting statements into two Execute SQL tasks in your package, one for the DROPs and one for the ADDs.
To deal with Identity columns, check "Enable identity insert" on the Options tab of the Transform Data tasks.
Here's the FK hierarchy script:
/******************************************************************************
This script will run through the foreign keys on tables to produce a hierarchy
of the tables in a database. Use this report when determining the order for
deleting data from tables or for loading data into table.
The heirarchy produced will be :
0 Tables that have no FK relationships at all, as either as 'parents' or
'children'
1 Tables which are at the top of the tree, and have no 'parents', only
'children'
2 Tables which have a parent at the heirarchy 1 level
3 Tables which have a parent who is a child of a top level parent
4... Tables which have a parent who is a child of a child of a top level parent
Also included is a section to generate drop constraint scripts. Scripts must be run
from lowest level to highest(...3,2,1,0). Scripts are generated in that run order.
There is another section to generate add constraint scripts. These scripts must be run
from top level down (0,1,2...). Scripts are generated in that run order.
See further comments within the script.
*******************************************************************************/
SET NOCOUNT ON
DECLARE
@intCounter INT,
@intRowCount INT
CREATE TABLE #Hierarchy
(Hierarchy INT,
Child VARCHAR(100),
Parent VARCHAR(100),
FKName VARCHAR (100))
-- Set the variables
SET @intCounter = 1
SET @intRowCount = 1
/************************************************************************************
Populate the table with all child and parent table relationships, and the name of the
foreign key relationships between them. Initially sets all records hierarchy level
to 1.
************************************************************************************/
INSERT INTO #Hierarchy
SELECT DISTINCT 1 AS 'Hierarchy', S1.name AS 'Child', SO.Name AS 'Parent', S2.Name AS 'FKName'
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.sysobjects SO
ON FK.rkeyID = SO.id
INNER JOIN dbo.sysobjects S1
ON FK.fkeyID = S1.id
left JOIN dbo.sysobjects S2
on FK.constid = S2.id
/************************************************************************************
Filters through the records to update the hierarchy level based on the current level
and the existance of the parent table in the child column. This establishes the hierarchtical pattern.
1st pass
- counter set to 1
- updates hierarchy to 2 where parent exists as a child and heirarchy = 1
2nd pass
- counter set to 2
- updates hierarchy to 3 where parent exists as a child and heirarchy = 2
3rd pass
- counter set to 3
- updates hierarchy to 4 where parent exists as a child and heirarchy = 3
Continues until no more levels where @@Rowcount will be 0.
************************************************************************************/
WHILE @intRowCount <> 0
BEGIN
UPDATE #Hierarchy
SET Hierarchy = Hierarchy + 1
WHERE Hierarchy = @intCounter
AND Parent IN (SELECT DISTINCT Child
FROM #Hierarchy
WHERE Hierarchy = @intCounter)
SET @intRowCount = @@Rowcount
SET @intCounter = @intCounter + 1
END
/************************************************************************************
This next section filters through all of the records determines the hierarchy level
and inserts a reporting record for those tables that have no Foreign Key relationships
or are the top level Parent table.
************************************************************************************/
-- Add the tables that have no Foreign Key relationships
INSERT INTO #Hierarchy
SELECT -1, [name], ' No FK ', ' - '
FROM dbo.sysobjects
WHERE [name] NOT IN (SELECT DISTINCT Parent FROM #Hierarchy)
AND [Name] NOT IN (SELECT DISTINCT Child FROM #Hierarchy)
AND xtype = 'U'
-- Add the tables that are top level Parents only
INSERT INTO #Hierarchy
SELECT DISTINCT 0,' - ' , Parent, ' - '
From #Hierarchy
WHERE Parent NOT IN (SELECT Child FROM #Hierarchy)
AND Hierarchy <> -1
-- Add 1 to adjust the hierarchies to start at 0
UPDATE #Hierarchy
SET Hierarchy = Hierarchy + 1
-- Display the results
SELECT DISTINCT Hierarchy, Child, Parent, FKName
FROM #Hierarchy
ORDER BY Hierarchy, Child, Parent
-- Generates Drop Constraint Scripts
select 'ALTER TABLE ' + cast(child as char(40)) + 'DROP CONSTRAINT '
+ cast(FKName as char(50)) + char(13)
from #Hierarchy
ORDER BY Hierarchy desc, Child, Parent
-- Generates Add Constraint Scripts
select 'ALTER TABLE ' + cast(child as char(40)) + 'ADD CONSTRAINT '
+ cast(FKName as char(50)) + 'FOREIGN KEY ' + '(' + fc.name + ') ' + 'REFERENCES '
+ cast(Parent as char(30)) + '(' + rc.name + ') ' + char(13)
from (select distinct constid, fkeyid, rkeyid, fkey, rkey from sysforeignkeys) as FK
join sysobjects c on c.id = FK.constid
join sysobjects t on t.id = FK.fkeyid
join sysobjects rt on rt.id = FK.rkeyid
join syscolumns fc on fc.colid = FK.fkey and fc.id = FK.fkeyid
join syscolumns rc on rc.colid = FK.rkey and rc.id = FK.rkeyid
join #Hierarchy H1 on t.name = child and rt.name = Parent
ORDER BY Hierarchy, Child, Parent
-- Clean up
DROP TABLE #Hierarchy
Greg
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply