Using rowcount to update/delete chunks of data..

[Jase P Wells]

I'm wondering if I can safely use rowcount to limit the number of rows
when updating and deleting data.

For example, say I have a large table (10000+ rows) and I want to move all
its rows to another table in small chunks. I might try this:

set rowcount 1000
go

select * into mytable_temp from mytable
go
delete from mytable
go

insert mytable_temp select * from mytable
go
delete from mytable
go

...

The question is: will the rows affected by the delete be the same rows
affected by the select/insert? (That is, assuming the two commands are
back-to-back and there are no intervening data modifications.)

Thanks,

jase

--
Jase P Wells, MTS (the Web & Scopus guy)
Alta Group of Cadence Design Systems Email: ja...@cadence.com
555 North Mathilda Avenue Phone: (408) 523-8706
Sunnyvale, California 94086 Fax: (408) 523-4601
--
To avoid junk-mai, my address is munged in the Reply-To header.
To reply change the address to ja...@cadence.com before sending.

[Teresa Larson]

Jase P Wells (jase@edit_to_reply.com) wrote:
: I'm wondering if I can safely use rowcount to limit the number of rows

: when updating and deleting data.

Yes.

: For example, say I have a large table (10000+ rows) and I want to move all

: its rows to another table in small chunks. I might try this:
:
: set rowcount 1000
: go
:
: select * into mytable_temp from mytable
: go
: delete from mytable
: go
:
: insert mytable_temp select * from mytable
: go
: delete from mytable
: go
:
: ...
:
: The question is: will the rows affected by the delete be the same rows
: affected by the select/insert? (That is, assuming the two commands are
: back-to-back and there are no intervening data modifications.)

In theory, no. Relational means unordered sets, so there are no
guarantees. In reality, yes, but you need to add an ORDER BY clause.
In the Sybase world, the above would be ok if you have a clustered
index on the table -- many people depend on the behavior of clustered
indexes on this type of thing. But it's best to be safe and include
the ORDER BY -- IMHO.

Hope this helps
Teresa Larson

_________________________________________________________________
/ Teresa A. Larson http://www2.ari.net/jmasino/ /
/ Bell Atlantic Voice: (301) 282-0224 /
/ 13100 Columbia Pike, A-3-3 Fax: (301) 282-9416 /
/ Silver Spring, MD 20904 Teresa....@bell-atl.com /
/________________________________________________________________/
#include <std_disclaimer>