Post reply

Determining table insert order

  • March 24, 2010 at 1:25 pm

    #231121

    Anyone got any tips or tricks for determining the correct order to load tables without violating foreign key constraints? I've got to spend the next 4 months flipping data from one schema into a completley new one. This is going to be a monster.

    I could look at the ERD and figure it out but there are already 99 tables and it's just getting bigger by the week.

    I used RedGate Data Compare to compare a preloaded Db to an empty one and look at the synchronization script that it creates. It drops all the constraints and re-adds them at the end. I don't have a problem with doing this but still need to know the insert order or when re-enabling the constraints I will get violations if I don't load every single table, and I won't during development of this ETL job.

    Thanks,

    dnash

  • March 24, 2010 at 1:54 pm

    #1139211

    This link provides some information that might help you out.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 24, 2010 at 1:55 pm

    #1139213

    Most tools that do stuff like this drop the FKeys, insert the data and then re-add them.. Once you get enough tables involved, I'm thinking this isn't such a bad idea..

    CEWII

  • March 24, 2010 at 2:03 pm

    #1139217

    The script on the link below will give you what you want.

    Find Table Reference Levels

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

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

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