Not sure if this is really the right forum but here goes. I am using SQL 2012.

I am trying to set up a T-SQL script that will drop then create all the tables, keys, foreign keys and constraints in a database. It will also load all the initial data to reference tables.

When I promote this database, I want to just tell the administrators 'run this script and it will do everything'.

Does anyone have any general tips on doing this? It seems the order of operations is critical to avoid dependency problems.

One particular issue I encountered involves 2 tables having a foreign key relationship. I have an Investigator table and a Team table. TeamId in the Investigator table is a FK to the Team table. The teams and Investigators are all predefined. But how do I set up the tables? I need to create the Team table first so I know the primary keys (which are auto assigned). Then I can set up the Investigator table - but how do I know which FK to put in each row of the Investigator table?

 You can try using Generate Scripts functionality in SQL Server to generate all of the scripts with all relevant PK/FK and constraints to make that part of your job easier. The problem is the loading of the two tables as you have described above.

If the table's data is predefined i would recommend using SET IDENTITY INSERT ON functionality. That way you can explicitly insert values for your PK (TeamID) into your Team table and and then re-use the same values for your FK in the Investigator table. 

MSDN Link for Identity Insert is below for your convinience

https://msdn.microsoft.com/en-us/library/ms188059.aspx

Let me know if that helps

• Proposed as answer by Naomi NModerator 2 hours 59 minutes ago