Requirement:
You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.
The sequence for DELETE is Child-before-Parent.
The sequence for INSERT is Parent-before-Child.
I created a quick script in response to a forum post. The script is not the best out there because –
- It does not handle circular references in relationships
- It takes 30 seconds or more if run on a database with hundreds of tables
I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.
Download script at –
http://gallery.technet.microsoft.com/Parent-Child-Table-45fa5268
Further reference:
For a faster (but not with all these output columns) script, look at Erland Sommarskog’s response in the forum thread.
Script output would look like the example below –
