Posted by Erland Sommarskog on 09/30/97 11:17

akej via SQLMonster.com (forum@nospam.SQLMonster.com) writes:
> Now in case one row was deletes from table1 i need to delete all t1ID's
> from the connection tables (each of connection may have e.g. 200000 rows).
> If u use delete on cascade it's take a lot of time, i want to delete let's
> say 1000 rows till all irrelevant rows will be deleted.

Well, if you have FK constraints from the connections tables to the
main table, you cannot delete the connection from that table until all
references from the connection tables are gone. So while you could batch
things like:

SET ROWCOUNT 10000
WHILE EXISTS (SELECT * FROM tbl2 WHERE tl1D = @id_to_delete)
DELETE tbl2 WHERE tlID = @id_to_delete
SET ROWCOUNT 0

I can't really see that it will help you.

You could of course drop the foreign-key constraint, and the start some
background process that deletes the rows in the other table, but that's
a pretty wild thing to do.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Next in forum: Re: Need some help with design of tables/views
• Prev in forum: Re: Still need help creating a DB by RESTORE
• Thread view: Re: How to delete a big amount of data

[Reply to this message]