1

I've got a moderately large sql server db for which some data is imported from an external system, and some is entered through the front end or set up when the app is set up. I want to delete all the data from all the tables we import to, but leave the data on the other tables. Everything of course has foreign keys/relationships/constraints set up.

First I tried manually deleting in the proper order...it was a slow process to determine the order, and the delete itself ran very slowly. - is there a way to determine the delete order that won't run up against constraints?

I tried truncate, but that required dropping all the constraints, and I was concerned about getting them all back on without missing anything.
--is there a way to via script to drop them all then bring them all back? Last time I ended up just creating an empty one, then bringing data over with visual studio database compare which was also slow and painful.

What's the best way? Any tips? I'm a .net dev that does many stored procedures but not good with the admin or DBA type stuff.

migrated from stackoverflow.com Mar 16 '16 at 4:48

This question came from our site for professional and enthusiast programmers.

0

Dropping and then recreating all of the foreign keys shouldn't be a problem because you should have them all in your source control system. If that's not the case then you have much bigger problems.

You can generate DROP/CREATE scripts through SSMS. If you right-click on your database then you can go to Tasks->Generate Scripts... and select the tables that you need through the wizard. You'll have to do a lot of cut and paste to get just the FKs. Alternatively, you can dig down to the FKs in the object tree, right-click each one, and generate a script from that menu.

In either case, it sounds like your deploy process for your production system is in some bad shape.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy