How can we Empty the whole tables by one single query

[Niladri_Biswas]

truncate table tblName :)

Niladri Biswas

[David Mujica]

Drop database: The DROP DATABASE statement is used to delete a database. Everything is deleted ... tables, views, indexes, stored procedures, etc Drop Table: The DROP TABLE statement is used to delete a table. Truncate Table: Delete the data inside the table, and not the table itself Use this as reference: http://www.w3schools.com/SQl/sql_drop.asp[^]

[lrsalunkhe]

Respected, But I have given the Relations to Tables. Thanks.

[_Damian S_]

Then do it one table at at time, starting from the leaf tables... eg: Persons have one or more Addresses. Delete Addresses first, then Persons.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[Niladri_Biswas]

You could have specified that in the post.. How can one come to know that you want to perform a CASCADE DELETION ? e.g.

CREATE TABLE tblPK
( id int primary key )

CREATE TABLE tblFK
(id int,
name varchar(50),
age int
FOREIGN KEY (id) REFERENCES tblPK ON DELETE CASCADE)

:)

Niladri Biswas

modified on Wednesday, November 4, 2009 12:51 AM

[Mycroft Holmes]

In 20+ years of database design and development I've never actually used cascade delete, I wonder why!

[_Damian S_]

Mycroft Holmes wrote:

I wonder why!

Because you are scared of it? :laugh:

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[Mycroft Holmes]

I always envisage someone going to the settings table and using cascade delete on the Standard Customer record or something. Scared does not even cover it, terrified is closer.

[_Damian S_]

Meh... don't let users have access to the tables... have a good backup strategy!! ;-)

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[J4amieC]

Thats even the worst side effect. Imagine a header table with, lets say, 1000 records in it. Each header has 100,000 (badly indexed) child records. Cascade delete is turned on. Someone comes along and says "Ah header table only has 1000 rows, it will be easy to delete a record from there". Database reduced to treacle.

[Ashfield]

Probably for the same reason as I haven't - we are control freaks who like to explicitly delete things when we want to, not sudenly finding it happening 'on its own' :)

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP