August 1, 2007 at 9:56 am
I tried importing my production data to development, every time i do this i get error for many tables and some tables get imported. what I assume for the cause of error is if there are any tables with relationships,FK's there should be an order of tables to import.
Like a parent table has to be imported first before the child table(correct me if am worng)
What is the best way to get production data into developemt by deleting existing data in devlopment. I dont want to do a restore, just need to do it from DTS which i can use for future.
August 1, 2007 at 6:22 pm
Either drop the foreign key constraints before importing and recreate them after importing or import the tables in the corrrect order (parent then child, as you stated).
Here's a script I've used to get the hierarchy of foreign keys in a database.
Greg
/******************************************************************************
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply