Disable constraints/triggers in SQL Server to populate with seed data and re-enable

I have an atypical scenario. I am designing a small new SQL
Server database with general elements of a typical relational design. Let us
say, this includes a good dose of PK, FK, check constraints and triggers. Once
I have the shell of the database ready (with no data), now comes the difficult
part: I have to populate the tables. Furthermore, I have to have the data
itself as a script with INSERT statements.

Consider these tables for example:

Relational_Tables_With_Joins

There are a lot of FK’s crisscrossing the diagram. I know I
can populate these individual tables by chipping away at the details and
filling portions of data that I know, in the order I feel comfortable filling.
However, the FK’s will prevent me. The FK’s are going to force me to populate
the referenced tables first before populating the referencing tables. The
triggers may fail because we are doing this in a piecemeal fashion. At the end
of the day, I will have everything picture perfect, but right now, the
constraints and triggers are in my way. Unless I do it completely right at the
get go, I cannot even proceed. It might as well have been this instead!

Relational_Tables_With_Joins_Confused

In this case, I am seeding the tables with data. However, I
can think of many other cases. Below, I am showing one way I can get around the
problem without compromising anything:

  1.         Disable all FK and check constraints on all the tables
  2.         Disable all the triggers on all the tables
  3.         Load the data in piecemeal in any table order but somehow get it
    all right at the end
  4.         Enable all triggers on all the tables
  5.         Enable all FK and check constraints on all the tables
  6.         At this point, I can have SSMS script out the final data as
    INSERT statements in the right table order

sp_MSForEachTable

Doing the above is not very difficult using an undocumented
(but widely used) stored procedure “sp_MSForEachTable”

It simply replaces the process of having to loop through
every table in the database with a FOR or WHILE loop. Below, I show how I use
sp_MSForEachTable to do what we need. The “?” below gets substituted with the
table name as it loops through all tables in the database.

1.       Disable all FK and check constraints on all the tables

EXEC
sp_msforeachtable

  @command1=“print ‘Disabling constraints on: ?'”,

  @command2=“ALTER TABLE ? NOCHECK CONSTRAINT all”;

2.       Disable all the triggers on all the tables

EXEC
sp_msforeachtable

@command1=“print
‘Disabling triggers on: ?'”,

@command2=“ALTER
TABLE ? DISABLE TRIGGER all”;

3.       Load the data in piecemeal in any table order but somehow get
it all right at the end

…really up to you how you do this

4.       Enable all triggers on all the tables

EXEC
sp_msforeachtable

   @command1=“print
‘Enabling triggers on: ?'”,

   @command2=“ALTER TABLE ? ENABLE TRIGGER all”;

5.       Enable all FK and check constraints on all the tables

EXEC
sp_msforeachtable

   @command1=“print
‘Enabling constraints on: ?'”,


@command2=“ALTER TABLE ? WITH
CHECK CHECK CONSTRAINT all”;

 

Tip: To just activate constraint and not validate the
populated data use “WITH NOCHECK”. To validate existing data and to activate
constraint use “WITH CHECK”

6.       At this point, I can have SSMS script out the final data as
INSERT statements in the right table order

Right click on the database

Tasks -> Generate Scripts

In the wizard, pick the highlighted option (may vary
slightly based on version)

SQL_Server_Script_Table_Data

At this point, I have a script that not only creates the
tables but also the INSERT’s to go along with it. The concept is the same for
Oracle although the procedure is slightly different.

WARNING: Please do not follow this procedure for
production data cleanup. This method is to help ease the pain of data
population “during development”, without causing any disruption to the
structures.

References:

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s