replacement for SET ROWCOUNT
Posted on 2008-11-18
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 )