Need to insert a row into tableX? Well, you'll first need to enter rows into required foreign key column tables and their parents too.
This procedure, ShowTableParents, will display in inverted tree order all the multiple optional and required foreign key/parent table rows. You must customize this to exclude all denormalized column foreign keys to avoid infinite loops.
I could have written this more simply as a recursive function but it would run slower. This is written using a table variable as a stack to simulate recursion.
-- try this:
exec dbo.ShowTableParents 'Sales.Store',0,'|--';
-- Here is the output; notice the third line Sales.SalesPerson is not required, thus, nor any table below its level: