September 28, 2003 at 1:32 am
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
September 28, 2003 at 6:28 am
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
September 28, 2003 at 1:53 pm
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
September 28, 2003 at 3:56 pm
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
September 28, 2003 at 5:59 pm
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.
September 29, 2003 at 6:09 am
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