[RESOLVED] delete all data from all table

Hi all :wave:
I have a database in SQL SERVER 2005
that contaion 35 table
I want to clear all the table data using one query plese guide me!

I do not want to delete using while loop!

Thanks

I don't use SQL Server, but don't know of anyway to delete all from 35 tables in one query. Maybe there's a way to copy just the structure of the db to another one. What's the problem with using a loop?

Delete the database and then create it again. No loop required.

Or, rather than deleting the database, drop all the tables and recreate them.

My I ask what the issue with using a loop might be?

Create a Script that will drop and then recreate the tables. Don't forget about indexes and primary keys.

How about just...

Code:

---

Truncate Table SomeTableA; Truncate Table SomeTableB; Truncate Table Sometable C;...

---

and so on

If that's a problem for you then create a stored procedure called TruncateAllMyTables and put the 35 truncate statements in that SPROC - and just execute it.

If you have lots of referential integrity between tables you might need to use DELETE FROM SOMETABLEA instead - but of course that is lots and lots slower and use the log file (which will expand it greatly).

Wouldn't Truncate have issues if there are FK relationships to of the table? I know that Oracle will not allow a tracate if the table is a Parent in a FK relationship.

That is what I meant by...

Quote:

---

Originally Posted by szlamany

If you have lots of referential integrity between tables you might need to use DELETE FROM SOMETABLEA instead - but of course that is lots and lots slower and use the log file (which will expand it greatly).

---

Quote:

---

Originally Posted by szlamany

How about just...

Code:

---

Truncate Table SomeTableA; Truncate Table SomeTableB; Truncate Table Sometable C;...

---

and so on

If that's a problem for you then create a stored procedure called TruncateAllMyTables and put the 35 truncate statements in that SPROC - and just execute it.

If you have lots of referential integrity between tables you might need to use DELETE FROM SOMETABLEA instead - but of course that is lots and lots slower and use the log file (which will expand it greatly).

---

Thanks sir for guidence
I want to ask what is best to do?

Truncate or delete statement??

If there is referential integrity between tables then you must use Delete on the Parent side of the relationship. Truncate on any other table.

As Gary said - TRUNCATE will fail with an error if there are foreign keys and what not.

Try TRUNCATE TABLE - if it fails - change it do DELETE FROM.

Thanks for guiding