Working on a customer project the other day, I needed to load some data into a database that had been scripted and re-created. The newly created database already had foreign keys in place which means for loading data (this was from SSIS), we had 2 options:
1) drop the foreign key constraints, load the data, re-apply the constraints
2) load the data in the correct order so that parent tables are loaded first, followed by children, followed by grandchildren, etc…
I decided to give option 2 a try. In our work we were dynamically generating an SSIS package in C# – so we had some flexibility in deciding how to load the data. So what I needed was a way to identify the correct order described in option #2.
The idea is fairly straightforward. We needed to find the parent tables that have references to them but don’t reference anything themselves. Then find all the objects that reference those tables, and so on… The system view sys.foreign_keys is our primary tool for this. We need to track changes at each level because a “child” table could reference tables at difference levels. In this case, we’re mostly interested in the lowest level reference to be sure all of it’s parent tables are loaded first. Finally, I found that with this method, self-referencing tables are problematic. In this version, I simply exclude those. If the data is loaded into that table in order, everything should be fine.
Here is the script I wrote for this – hope it helps:
DECLARE @FK_TABLE_ORDER TABLE ( FKLEVEL INT, OBJECTID INT ) DECLARE @CHILDCOUNT INT, @CURRLEVEL INT SELECT @CHILDCOUNT = 1, @CURRLEVEL = 0; WITH Top_Level_Tables AS ( SELECT t.object_id, t.schema_id, f.parent_object_id FROM sys.tables t INNER JOIN sys.foreign_keys f ON t.object_id = f.referenced_object_id ) INSERT INTO @FK_TABLE_ORDER SELECT DISTINCT 0, object_id from Top_Level_Tables WHERE object_id NOT IN (SELECT parent_object_id FROM Top_Level_Tables) SELECT @CHILDCOUNT = ( SELECT COUNT(*) FROM sys.foreign_keys WHERE referenced_object_id IN (SELECT OBJECTID FROM @FK_TABLE_ORDER WHERE FKLEVEL = @CURRLEVEL) ) WHILE (@CHILDCOUNT > 0) BEGIN SELECT @CURRLEVEL = @CURRLEVEL + 1; INSERT INTO @FK_TABLE_ORDER SELECT DISTINCT @CURRLEVEL, parent_object_id FROM sys.foreign_keys WHERE referenced_object_id IN (SELECT OBJECTID FROM @FK_TABLE_ORDER WHERE FKLEVEL = (@CURRLEVEL - 1) ) AND referenced_object_id IS NOT NULL AND referenced_object_id != parent_object_id SELECT @CHILDCOUNT = ( SELECT COUNT(*) FROM sys.foreign_keys WHERE referenced_object_id IN (SELECT OBJECTID FROM @FK_TABLE_ORDER WHERE FKLEVEL = @CURRLEVEL) ) END INSERT INTO @FK_TABLE_ORDER SELECT 0, t.object_id FROM sys.tables t WHERE t.object_id NOT IN (SELECT OBJECTID FROM @FK_TABLE_ORDER) SELECT OBJECT_NAME(OBJECTID) as 'Table Name', MAX(FKLEVEL) as 'Load Order' FROM @FK_TABLE_ORDER GROUP BY OBJECTID ORDER BY [Load Order] ASC