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

How do I drop all data w/in a sql table but keep the table? I need to keep the table/s but clear the data from w/in the tables.
QuackdeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
Pawan KumarDatabase ExpertCommented:
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.
QuackdeveloperAuthor Commented:
what would the statement be though?

delete or truncate:
delete from tablename
(sample data, sample data 2);
 commit:
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

QuackdeveloperAuthor Commented:
keep getting an error message that the sql command isn't properly ended
Pawan KumarDatabase ExpertCommented:
Use one of them like below.

Truncate table tablename
Jim HornMicrosoft SQL Server Data DudeCommented:
>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.
QuackdeveloperAuthor Commented:
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
Pawan KumarDatabase ExpertCommented:
Hi
Please use

Truncate table tablename

Or below

Delete from tablename

- you need to provide the tablename of ur actual database table.
Jim HornMicrosoft SQL Server Data DudeCommented:
>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.
Scott PletcherSenior DBACommented:
Try this first:

TRUNCATE TABLE [your_table_name_here];
TRUNCATE TABLE [your_table_name2_here];
...

If that doesn't work, then do this:
DELETE FROM [your_table_name_here];
DELETE FROM [your_table_name2_here];
...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dorababu MSenior Software EngineerCommented:
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)'

Open in new window

Pawan KumarDatabase ExpertCommented:
Do not use this sp_MSForEachTable. This is undocumented. Also why so much of complicated code when single command works.
Dorababu MSenior Software EngineerCommented:
If there are 100 tables then that will be useful and I am using the same query when ever needed.
Pawan KumarDatabase ExpertCommented:
for that we can use a while loop.
Scott PletcherSenior DBACommented:
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.
DBAduck - Ben MillerPrincipal ConsultantCommented:
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.
QuackdeveloperAuthor Commented:
worked after a little adjustment...thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.