Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WalkerDA
Yak Posting Veteran
61 Posts |
 Posted - 2004-07-08 : 21:20:00
|
| What is a good way of dropping tables that are referenced by other tables? This is a development DB and the data is very bad, I want to port over data from a DB with live data. I figure I can always save the data then drop the referencing tables first which sounds like a better way but I am not sure of what normal/best practices would be in a case like this one. I'm sure someone has already toppled this situation! Any thoughts?Derrick |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-07-08 : 21:30:57
|
| Do you mean that you want to drop the child tables, then drop the parent tables? |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-07-08 : 23:31:13
|
| Unless the schema changed you'd only want to drop the relationships, then truncate the tables, reload the data and re-establish the relationships. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-09 : 08:08:36
|
| I do this by using GENERATE SQL in Enterprise Manager. The DROP FOREIGN KEY statemetns are all neatly in a block near the top of the generated script, and the CREATE FOREIGN KEY ones are in a block near the bottom.Cut,Paste&Execute the DROP stuff, deleted your duff-data, copy over your good data, then Cut,Paste&Execute the CREATE stuff.(Note that importing the data BEFORE creating the FKs means that the order in which you import the tables is not important)Kristen |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-07-09 : 10:38:05
|
| Ok, so by dropping the constraints (via Generate Scripts) will give me the flexibility to truncate the tables, fill and then re-add the constraints afterwards?Derrick |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-09 : 12:09:14
|
| Yup, that's it. In fact much better to TRUNCATE rather than DELETE, and you'll be able to do that without those pesky FK relationships getting in the way!Kristen |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-07-09 : 12:34:43
|
| Thanks..that's exactly what I needed to do.Derrick |
|
|
|
|
|
|
|
|
Dropping Tables