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 17
dbaSQLAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the replacement for the ROWCOUNT is the TOP keyword as part of the query.

DELETE TOP 1000 ...
0
 
momi_sabagConnect With a Mentor Commented:
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
 
thiyagukConnect With a Mentor Tech Lead Commented:
In sQlserver, you can use Rowcount   to delete
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.