How do I drop all data w/in a sql table but keep the table?

TRUNCATE TABLE table_name would be the fastest as it's minimally logged, but there are a couple of restrictions such as foreign keys that may prevent you from being able to do this.

DELETE FROM table_name would also work, and it's fully logged so slower.

We can use delete and truncate commands to remove all data from the tables.

Truncate table tablename

Delete from tablename

Truncate is faster than delete as it is minimally logged and will reseed identity column if any is there.

what would the statement be though?

delete or truncate:
delete from tablename
(sample data, sample data 2);
 commit:

keep getting an error message that the sql command isn't properly ended

Use one of them like below.

Truncate table tablename

>what would the statement be though?
We already told you.

Check out Microsoft's explanations of TRUNCATE TABLE and DELETE, and if you scroll down to the bottom of each article there will be multiple examples.

thanks...you didn't actually tell me though...not trying to be harsh but turnabout is fair play...I'm very new to sql and doing this since my developer is out for the holiday...reaching out to experts is the best option I have...don't expect me to be an expert and know what you mean by simply stating that's what I need to do w/o code examples

Hi
Please use

Truncate table tablename

Or below

Delete from tablename

- you need to provide the tablename of ur actual database table.

>thanks...you didn't actually tell me though
Actually I did, but since you didn't tell us the table name or any other code we could use to better answer your question then you'll need to subsitute table_name for whatever that is.   You wouldn't believe how many times we get asked to 'fix my code' without any code given to work with.

For a highly entertaining read that works well with your situation check out Top 10 Ways to Ask Better Questions.

You can try this if there are relations between tables

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO


exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 0)'

Select allOpen in new window

Do not use this sp_MSForEachTable. This is undocumented. Also why so much of complicated code when single command works.

If there are 100 tables then that will be useful and I am using the same query when ever needed.

for that we can use a while loop.

To the original poster:

DON'T arbitrarily disable all constraints on a table. It's extraordinarily dangerous, and it's often very difficult to get them all properly working again once you do so.  You have to get SQL to "trust" them again, or they're worthless to your queries, but still cause overhead.

Instead:
IF the table has so many rows you really need to use TRUNCATE rather than DELETE,
AND there are foreign key constraints that prevent you from using TRUNCATE
AND you can't instead just delete in batches instead
THEN let me know and I'll give you an alternative that disables only the conflicting constraints.

As has been said you can use either TRUNCATE or DELETE.

Truncate table is a command that behind the scenes just deallocates the pages and logs the deallocations, where DELETE will log the row deletes.  The syntax has been shown in above posts. The decision will really be about any constraints (FK, etc) that will prevent you from using TRUNCATE TABLE and then you will use DELETE FROM tablename.

Not sure there is anything more to say here.

worked after a little adjustment...thanks