[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

replacement for SET ROWCOUNT

Posted on 2008-11-18
21
Medium Priority
?
548 Views
Last Modified: 2012-06-21
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 )

0
Comment
Question by:dbaSQL
21 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 22983854
the replacement for the ROWCOUNT is the TOP keyword as part of the query.

DELETE TOP 1000 ...
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 400 total points
ID: 22983874
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
 
LVL 7

Assisted Solution

by:thiyaguk
thiyaguk earned 400 total points
ID: 22983875
In sQlserver, you can use Rowcount   to delete
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Author Comment

by:dbaSQL
ID: 22983914
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22983932
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22983938
exactly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22983969
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22983993
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984004
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22984055
if you load large amounts of data, why don't you use dts / ssis / bcp ?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984122
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984133
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22984142
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984150
yes, i want to make it waaaay faster.  please show me an example, momi sabaq?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984235
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984273
nope... i fill it with 1000, then fail because 'temp' already exists.  please advise
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984479
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984595
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 22984756
Please advise.  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 23003492
>>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
 
LVL 17

Author Comment

by:dbaSQL
ID: 23316997
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question