How do I go around constaints while Importing data

  • Hi,

    My problem is When I'm trying to Importing data to an exist table ,an error message raise telling that there is a conflict with delete constraint.

    I'm using SQLSERVER 2000 and I'm one of DBA team in my orgnization .we aften recieve request from developers to copy fresh data into their area.

    Now the question is ,Do I have to drop all the constraints related to the table before I copy new data? Is there any easy way to do this?

    thanks

  • You don't have to drop and recreate them, just use an alter table statement and disable and then re-enable them. If this is a common practice you might create a DTS package that workflows these statements in. Something like this:

    ALTER TABLE tablename NOCHECK CONSTRAINT constraintname

    insert data

    alter table tablename check constraint constraintname

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • Or just restore an entire copy of the db - sometimes faster, depends on how much data they really need. If you do the restore, make sure they have their changes scripted out or in source control first!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • As there's no such thing as a "delete constraint," I'm guessing that foreign key constraints on other tables preventing you from deleting the old values prior to importing the fresh data. You will need to disable those constraints on the other tables before deleting the data. This could, however, leave you with disconnected foreign key values.

    If you do indeed mean check constraints, any of the bulk processes, including DTS, will ignore those constraints by default, so, if the import is via one of these, you will not need to drop or disable any constraints.

    --Jonathan

    Edited by - jonathan on 09/28/2003 3:56:58 PM



    --Jonathan

  • If you are importing data to a table, what does it have to do with deleting?

    If you delete the old data for new importing,the problem you have is very likely a fk constraint. You can alter the table and set on the ON DELETE CASCADE.

  • I disable all FK and triggers when I refresh data only for development:

    To disable all Triggers and FK's:

    -- Disable all constraints and triggers

    sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    go

    sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

    go

    Then I'll run DTS import data and finally re-enable all FK and Triggers:

    --Enable all constraints and triggers

    sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"

    go

    sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all"

    go

Viewing 6 posts - 1 through 5 (of 5 total)

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