How to delete all data from table when foreign-key exists.

I would like to delete all data from the tables in my database so that I can
test in a clean environment. With no data, it makes creating controlled
data much easier.
I would normally use "truncate", but it seems that I cannot since the tables
I want to delete from have foreign key constraints.
So, I end up using the "delete" command; however, this takes forever since
most that tables have between 100000 and millions of records.
I would imaging the performance issue is related to the fact that "delete"
writes to the transition log while "truncate" does not.
How can I delete all data from a table with foreign key constructs quickly?Disable the foreign key constraints temporarily. What's the point of
keeping them there if you're deleting all the data?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||Another option would be to drop the tables and re-create them (in a
dependent order, of course).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>