Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in a tough situation, and looking for advice on how to proceed. I have to import some data from one environment to another. 80% of the records have been modified, so the plan is to simply delete all the existing records, and import the new ones.

Unfortunately this is a base table. Several other tables have FK's defined, pointing at this table. In order to delete the data, I need to either drop the FK's and recreate them when finished the import, or disable the constraints and re-enable them.

No records have been deleted. A copy of prod was made, and developers have tweaked the data and added new records.

Which approach would you take?

share|improve this question
1  
Wouldn't merge be an option here? –  Mikael Eriksson May 18 '12 at 15:24
    
Yes, but in this case the version of SQL server is SQL-2000. I fixed the tag. –  datagod May 18 '12 at 17:12
1  
3  
Ok. Then I guess the alternative is a update with a join followed by a insert with a where not exist. I would not drop or disable the FK's. I would use them to prevent me from messing up. –  Mikael Eriksson May 18 '12 at 19:17

1 Answer 1

up vote 1 down vote accepted

As recommended by Mikael Eriksson, I'd do the import without dropping/disabling the Foreign Keys.

The downside to this is if you want to delete any records who's values are referenced by an FK or want to update values such that the original value is no longer in the table and referenced by an FK.
In these cases you'd need to keep track of any such issues and decide how to handle each "conflict" (don't update/delete, alter the data in the other table or some other solution).

This will be quicker, easier and ensure data integrity compared to dropping/disabling the FKs, importing and then restoring the FKs.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.