replacement for SET ROWCOUNT

Per MSFT:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it.

what is the best means of handling this in batches, say 50,000 or so, to avoid blowing the tran log?

declare @start datetime, @stop datetime
set @start = '11/14/08'
set @stop = '11/15/08'

INSERT INTO historyTable (field1,field2,field3.......field12)
SELECT .....same twelve fields....
FROM database.dbo.currentTable c WITH (NOLOCK)
WHERE c.EventTime >= @start AND c.EventTime < @stop
AND NOT EXISTS(SELECT 1 FROM historyTable h
WHERE h.EventTime >= @start
AND c.orderno = h.orderno )

LVL 18
dbaSQLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the replacement for the ROWCOUNT is the TOP keyword as part of the query.

DELETE TOP 1000 ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
momi_sabagCommented:
in your case you need to use the top as angeliiii mentioned but you also need to keep the primary key of the table from where you select so you would be able to reposition the next time

declare @eventId int
declare @start datetime, @stop datetime
set @start = '11/14/08'
set @stop = '11/15/08'

INSERT INTO historyTable (field1,field2,field3.......field12)
SELECT top 1000 .....same twelve fields...., @eventId = eventId
FROM database.dbo.currentTable c WITH (NOLOCK)
WHERE c.EventTime >= @start AND c.EventTime < @stop
AND NOT EXISTS(SELECT 1 FROM historyTable h
WHERE h.EventTime >= @start
AND c.orderno = h.orderno )
order by eventid

while (@@rowcount >0)
begin
INSERT INTO historyTable (field1,field2,field3.......field12)
SELECT top 1000 .....same twelve fields...., @eventId = eventId
FROM database.dbo.currentTable c WITH (NOLOCK)
WHERE c.EventTime >= @start AND c.EventTime < @stop
and eventId > @eventId
AND NOT EXISTS(SELECT 1 FROM historyTable h
WHERE h.EventTime >= @start
AND c.orderno = h.orderno )
order by eventid

end
0
thiyagukTech Lead Commented:
In sQlserver, you can use Rowcount   to delete
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dbaSQLAuthor Commented:
interesting.  is this (below) sufficient now, even with v2000?  and the same construct is applicable to INSERT, DELETE and/or UPDATE?

declare @eventId int
declare @start datetime, @stop datetime
set @start = '11/14/08'
set @stop = '11/15/08'

while (@@rowcount >0)
begin
INSERT INTO historyTable (field1,field2,field3.......field12)
SELECT top 1000 .....same twelve fields...., @eventId = eventId
FROM database.dbo.currentTable c WITH (NOLOCK)
WHERE c.EventTime >= @start AND c.EventTime < @stop
and eventId > @eventId
AND NOT EXISTS(SELECT 1 FROM historyTable h
WHERE h.EventTime >= @start
AND c.orderno = h.orderno )
order by eventid

end
0
dbaSQLAuthor Commented:
that SELECT TOP 1000  --- just like SET ROWCOUNT?  I mean, say i have 2million records to insert, update or delete, i'm going to batch thru them with SELECT TOP 1000 ?
0
momi_sabagCommented:
exactly
0
dbaSQLAuthor Commented:
any thoughts on the performance?  for the insert i posted, for example, but now using SELECT TOP.  i've got a daily insertion of 20 to 25M records or so, from current tables to history tables.  just very generally speaking (forget about indexes, concurrent activity, etc.), is that constructed optimally?
0
dbaSQLAuthor Commented:
i ask because i have one that has been running for near 3 hours.  have used the same code, same tables, many, many times before.  i'm needing to get this in place properly, very quickly, and get the data moved.  so, i get online this morning, checking my use of ROWCOUNT, which is what prompted this post.  if SET ROWCOUNT is no longer in next version, i want/need to do it right now.
0
dbaSQLAuthor Commented:
Also, is that order by actually necessary?  as i said, TONS of data.  the order by may be very costly.  is that actually necessary?

order by eventid
0
momi_sabagCommented:
if you load large amounts of data, why don't you use dts / ssis / bcp ?
0
dbaSQLAuthor Commented:
i intend to use ssis longer term, but right now this must function with the insertions.  i am at v2000 still...

well, actually... dts, tsql or bcp... whatever, it just needs to work.  a little background -- i had a nightly job in place for some time, that takes data from current tables and writes to historical, simply to keep the current structure smaller, and more manageable.  last week they had a problem upstream from me, the files were suspect, many problems, so i disabled my nightly job.... they've corrected their problems, now i need to play catchup, as the 'current' tables are not manageable at all.  a single day of data averages 20 to 25M records.

i MUST get a few days moved out of current, into historical, and i am trying to use the very same insertions i've been using for months --- they're not working.  very simply said, i've got three days of data to move from current to historical, where not exists.  (chances are, it all doesn't exist, but i can't risk a failure due to duplicate constraint violation)

if i bcp out/in, how am i going to do the where not exists?

if i dts/ssis, is it simply a data transfer from a to b, using the same parameters, where not exists?
0
dbaSQLAuthor Commented:
and, before this problem last week, i was keeping three days of data in current, everything else in historical.  like i said, a single day is around 20 to 25M records, whereas the historical table count is currently at 1,437,192,999.

longer term (v2008), this will be a partitioned structure.  right now it isn't, and i still must get this together like yesterday.  
0
momi_sabagCommented:
well
if you want to make your process faster, i'd suggest you get rid of the subquery

use a left outer join to populate a temp table with the pk value of records you need to move, and use that table to join the table that has the rows
0
dbaSQLAuthor Commented:
yes, i want to make it waaaay faster.  please show me an example, momi sabaq?
0
dbaSQLAuthor Commented:
surely this isn't it:

declare @eventId int
declare @start datetime, @stop datetime
set @start = '11/14/08'
set @stop = '11/15/08'

while (@@rowcount >0)
begin
SELECT top 1000 c.field1, c.field2, c.field3....c.field12 into temp
FROM database.dbo.currentTable c WITH (NOLOCK)  left outer join historytable h
on c.orderno = h.orderno
and c.eventtime >= @start
end
0
dbaSQLAuthor Commented:
nope... i fill it with 1000, then fail because 'temp' already exists.  please advise
0
dbaSQLAuthor Commented:
can you provide an example?

>>use a left outer join to populate a temp table with the pk value of records you need to move, and use that table to join the table that has the rows
0
dbaSQLAuthor Commented:
curious... how did you know i had an eventid on the table?
you say:  "  in your case you need to use the top as angeliiii mentioned but you also need to keep the primary key of the table from where you select so you would be able to reposition the next time  "

i just assumed my pk, which is orderno, but now as i look at your post again, it is eventid.

evenitid is my identity value --- which shall i use?


0
dbaSQLAuthor Commented:
Please advise.  
0
dbaSQLAuthor Commented:
>>well
if you want to make your process faster, i'd suggest you get rid of the subquery

use a left outer join to populate a temp table with the pk value of records you need to move, and use that table to join the table that has the rows

please provide an example, if you are able
0
dbaSQLAuthor Commented:
the example re getting rid of the subquery is still very welcome.  but, no reason to leave this open.  thank you for all of the feedback
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.