Delete all Records but Keep Table Structure 2

[jmiller79]

How do I delete all the Records in the table but keep the table structure the same. Any help would be great,

Thanks
Joe

 

[hneal98]

truncate table tablename

where tablename is the name of the table you want to delete the records from.

If you only want delete certain records, you can do this:

Delete tablename where ...

 

[SQLSister]

Note that using truncate table is dangerous, this is not a logged operation. Only use this if you have good recent backups or can readily reload the data from another source. If you want the deltion logged, then use the delte command without a where clause. And make darn sure you are in the right database on the right server and using the right table. If you are thinking you are clearing out the Customer table on your development server and you accidentally do it on the production server, then that is bad news even if you have a recent backup. People have lost their jobs for careless use of truncate table.

Questions about posting. See faq183-874

 

[despierto]

Very true SQLSister, but there is one other thing to mention about the difference between DELETE and TRUNCATE - if you use and identity column, TRUNCATE resets the identity to the seed (usually 1) - this is actually my determining factor in which one to use.

(side note - I've never used TRUNCATE TABLE on a production database... Only development systems)

 

[hneal98]

Great points.

 

[btturner]

Keep in mind that TRUNCATE TABLE is much more efficient in terms of performance. It flips a bit switch in the space map header and does NOT perform physical deletes row by row. (DELETE FROM performs row by row deletes and is a performance hog for very large tables)

 

[NoCoolHandle]

and (just to add to btturner..)
Be prepared for logging!
As Delete is a logged action 2 gig of data being truncated can take up more than the 2gig by way of logfiles.