Sequential Table insertion hierarchy

Question

Sequential Table insertion hierarchy

shamshad.ali

Hall of Fame

Points: 3850
More actions
March 3, 2016 at 6:40 am

#324658

I have to add data in tables for a fresh empty database, Don't know the structure but found Foreign Keys implemented in tables.
So I need some kind of script to find recursively tables based on FK references and provide the sequence such that we can add data in tables by that sequence one by one.
Those tables are lookup tables where we want to add data.
Shamshad

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

shamshad.ali (3/3/2016)
I have to add data in tables for a fresh empty database, Don't know the structure but found Foreign Keys implemented in tables.
So I need some kind of script to find recursively tables based on FK references and provide the sequence such that we can add data in tables by that sequence one by one.
Those tables are lookup tables where we want to add data.
Shamshad

Now we know your requirement, but what is your question?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 2

EXEC sp_msdependencies @intrans = 1

that will give you all objects in foreign key hierarchy order. it can be painfully slow or never ends if you have circular references (TableA -->TableB-->TableA)

basically, if you want to CREATE objects, you create them in that order, lowest to highest

to DELETE or DROP the objects it's from highest to lowest.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!