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?
>

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

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 IbisonPaul,
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=...ublic.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...
the
>
>.
>

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
>
>.
>