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?
>|||If you want an easy way which doesn't involve dropping the
constraints, you could use truncate table on all the
tables, but being careful about the order - apply the
command from the outside inwards relative to your schema
eg do the lookup tables first and the most central table
last.
Regards,
Paul Ibison|||Paul,
I'm afraid that truncate isn't allowed event if you have 0 rows in the
referencing tables. And even if you disable the FK, it isn't allowed. I
usually truncate the tables "at the bottom", these are typically the ones
with the most rows in. Then I just work my way "upward" and do DELETE on
those tables.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote in message
news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
> If you want an easy way which doesn't involve dropping the
> constraints, you could use truncate table on all the
> tables, but being careful about the order - apply the
> command from the outside inwards relative to your schema
> eg do the lookup tables first and the most central table
> last.
> Regards,
> Paul Ibison|||Thanks Tibor - had forgotten this behaviour.
Regards,
Paul Ibison
>--Original Message--
>Paul,
>I'm afraid that truncate isn't allowed event if you have
0 rows in the
>referencing tables. And even if you disable the FK, it
isn't allowed. I
>usually truncate the tables "at the bottom", these are
typically the ones
>with the most rows in. Then I just work my way "upward"
and do DELETE on
>those tables.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote
in message
>news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
>> If you want an easy way which doesn't involve dropping
the
>> constraints, you could use truncate table on all the
>> tables, but being careful about the order - apply the
>> command from the outside inwards relative to your schema
>> eg do the lookup tables first and the most central table
>> last.
>> Regards,
>> Paul Ibison
>
>.
>