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 |
|
zippy
Yak Posting Veteran
69 Posts |
 Posted - 2001-11-29 : 20:36:26
|
| Hi,I have been trying to loose all the data from my database so I can start afresh.I have been using TRUNCATE TABLE <TABLE NAME> to delete my tables.However with some of my tables it will not work as it says that there are constraints (foreign keys) on it. I took special care to make sure that I truncated other tables which used the data before I truncated the table which contained the data.At wits end I decided to try DELETE FROM <TABLE NAME> this worked first time every time!Is there some way that it handles a TRUNCATE that is so different from DELETE?I think I must be going nuts.Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-11-29 : 21:46:08
|
| Truncate is very different from a delete (although they do both empty the table). A truncate is normally only performed for maintenance and is certainly not to be included in normal code without a lot of thought - it is one of the things worth searching for if you are in charge of releases.The server cannot honour foreign keys with a truncate so they are not allowed.Also note that a truncate will invalidate any transaction log backups.If you must truncate then remove all the foreign keys, perform the truncates then add the truncates again.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
zippy
Yak Posting Veteran
69 Posts |
Posted - 2001-11-29 : 22:29:12
|
| "The server cannot honour foreign keys with a truncate so they are not allowed"and it can with a DELETE?I wouldn't use it normally, it is just I need to empty the database so that I can put some real data in it instead of test stuff.Is there a quick and easy way to drop all the relationships and then have them restored afterwards?Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2001-11-29 : 22:31:53
|
| HiWhy not just script your database and rebuild it ?Damian |
|
|
|
zippy
Yak Posting Veteran
69 Posts |
Posted - 2001-11-29 : 22:45:16
|
| I figured there would be a faster way to do it than that.Check out the worlds fastest computers at http://www.ocgurus.com |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-11-30 : 07:02:47
|
quote:
Is there a quick and easy way to drop all the relationships and then have them restored afterwards?
Well, you could grab sp_IndexKeyText.sql script from SWYNK. http://www.swynk.com/sqlscripts/index7.aspThat stored procedure will produce a script for dropping/adding all foreign key constraints (and primary keys, and indexes) in your database. Be warned though -- the stored procedure is VERY buggy... But at least it will give you something to start with, so you don't have to build the thing from scratch. In fact, the part that scripts out foreign keys almost works (it just needs some extra commas thrown in to make sure the generated script does not blow up on foreign keys, referencing more than one field in the parent table). |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-01 : 00:37:45
|
| >> and it can with a DELETE?yes - that's why you get foreign key errors.I had to do this on a production system and just built a script to drop all the foreign keys, do the truncates and add them afterwards.Other ways are - take a backup of an empty database and restore it.Drop all the tables and rebuld them all (this again has to drop in the correct order)Create a new database and transfer all the objects without data - or just script all objects and run the script.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Truncation Trouble!