Dealing with foreign keys when copying data

Question

Dealing with foreign keys when copying data

Ryan-209402

Hall of Fame

Points: 3707
More actions
July 22, 2008 at 7:49 am

#130170

With the old DTS data copy wizard in SQL 2000 you could copy data to a table that was being referenced by a foreign key and the copy operation would work fine. It must have automatically recognized that there was a foreign key and suspended enforcement during the copy. However, in SQL 2005 there is apparently no option to stop enforcement of foreign keys temporarily, thus the file copy bombs out with errors because it isn't able to truncate the destination table. We have low tech admins who perform lots of various data copies every day and whenever there is a foreign key referencing the destination table they have to first script it, delete it, and then recreate it when the copy is done. This creates a lot of room for human error. I realize that I could probably write a SSIS package which includes the functionality to temporarily not enforce the foreign keys but that is not ideal since I'd have to do it on a case by case basis, since every data copy operation is different. Any suggestions? Anything you can recommend is greatly appreciated.

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

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

Harley Arnett-275660 SSC Eights! Points: 859 More actions · Answer 1

Buy a copy of SQL Data Compare - it will compare data in any number of tables on the same server or separate servers and create a script to sync the tables - the script will automatically drop and recreate any foreign keys - it is invaluable!

Regards,

Harley

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 2

Thanks. I will download a evaluation copy and check it out. Anyone else have experience with SQL Data Compare?

Leon . Orlov . Ten Centuries Points: 1212 More actions · Answer 3

I've used SQL Compare starting with ver 5 for some time. It is a great tool for small to medium size databases. For large database you just have to do a smaller set of tables at the time.

It 150% ROI and worth every penny.. and more. SQL Compare.. must have for DBA :o)

cheers

~Leon

myheadhurts Ten Centuries Points: 1262 More actions · Answer 4

Provided you're not on a live production db, this may suffice, depending on structure of your db/constraints:

-- Turn off refer integ on a table

ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL

ALTER TABLE [TableName] DISABLE TRIGGER ALL

--load data here

-- Turn refer integ back ON

ALTER TABLE [TableName] CHECK CONSTRAINT ALL

ALTER TABLE [TableName] ENABLE TRIGGER ALL

This turns off checking for all tables, giving you more wiggle room:

-- Turn off refer integ

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

--load data here

-- Turn ON refer integ

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

You of course must ensure that your new data doesn't violate existing constraints.

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein