Post reply

DTS and FK Constraints - multiple tables

  • February 16, 2004 at 2:36 pm

    #59649

    Hi,

    Is there an easy/quick way to disable Foreign Key Constraints when copying & populating data from approx. 100 tables from a MSSQL 2000 database to another MSSQL 2000 server/database?

    THanks. Jeff

    Many thanks. Jeff

  • February 16, 2004 at 3:59 pm

    #494278

    Sorry for the bad news but AFAIK

    script them out, drop ,COPY DATA, recreate!

     

     


    * Noel

  • February 16, 2004 at 8:26 pm

    #494299

    I have put a Run SQL task that first drops an index, then a second Run SQL task that creates that index.  The first task runs before the transformation and the second after.

    You could do the same with the foreign key constraints.

    Russ

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 18, 2004 at 2:16 am

    #494478

    In my DTS I'm using the following, which seems to work well:

    ALTER TABLE <tablename> NOCHECK CONSTRAINT <fk name>

    Run delete/import script

    ALTER TABLE <tablename> CHECK CONSTRAINT <fk name>

  • February 18, 2004 at 8:05 am

    #494522

    We used the following to disable all constraints and triggers when importing data into existing SQL tables. Hope this helps. Richard

    -Disable CONSTRAINST & TRIGGERS

    DECLARE @TempSQL NVARCHAR(200)

      SET @TempSQL = 'sp_msforeachtable "Alter Table ? NOCHECK CONSTRAINT ALL"'

      EXEC sp_executesql @TempSQL

      SET @TempSQL = 'sp_msforeachtable "Alter Table ? DISABLE TRIGGER ALL"'

      EXEC sp_executesql @TempSQL

    --Enable CONSTRAINST & TRIGGERS

    DECLARE @TempSQL2 NVARCHAR(200)

      SET @TempSQL2 = 'sp_msforeachtable @Command1 = "Print ''?''", @command2 = "Alter Table ? CHECK CONSTRAINT ALL"'

      EXEC sp_executesql @TempSQL2

      SET @TempSQL2 = 'sp_msforeachtable @Command1 = "Print ''?''", @command2 = "Alter Table ? ENABLE TRIGGER ALL"'

      EXEC sp_executesql @TempSQL2

  • February 18, 2004 at 8:11 am

    #494526

    HI,

    Thanks to everyone's responses. One question .. the "?" .. is this a placeholder that will insert all the tables in the follwing:

    SET @TempSQL = 'sp_msforeachtable "Alter Table ? NOCHECK CONSTRAINT ALL"'

    Also, will this identify all tables or just user defined table objects?

    THanks. Jeff

    Many thanks. Jeff

  • February 18, 2004 at 8:17 am

    #494528

    Yes, this will identify all tables in db and will disable/enable all constraints and any triggers. Richard

  • February 19, 2004 at 1:13 pm

    #494776

    newbie ... many thanks!!! Works like a charm... Jeff

    Many thanks. Jeff

Viewing 8 posts - 1 through 7 (of 7 total)

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