Batch move/deletion of records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Batch move/deletion of records

Batch move/deletion of records

Rate Topic

Display Mode

Topic Options

Author

Message

ron.grace

Posted Wednesday, January 09, 2013 7:48 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 28, 2013 3:32 AM
Points: 18, Visits: 50

I'm trying to write a stored procedure that selects 100000 records at a time, deletes from 1 table and adds them to another. I need to have a limit on the number of records that get processed each time as I keep blowing the transaction logs as there's a huge number of records to process. What I've come up with is as follows, will this work, it would also be nice to a a running toal, something that says '100000 records processed', '200000 records processed' etc

WHILE (@@ROWCOUNT > 0)
BEGIN TRANSACTION
DECLARE @BatchSize int = 100000
INSERT TOP (@Batchsize) INTO [DataStaging_Archive]
SELECT ds.*
FROM DataStaging ds
LEFT OUTER JOIN DataStaging_Archive dsa
ON ds.URN = dsa.URN
WHERE dsa.URN IS NULL
AND ds.DateProcessed IS NOT NULL
DELETE FROM ds
FROM DataStaging ds
INNER JOIN DataStaging_Archive dsa
ON ds.URN = dsa.URN
EXEC TruncateLog
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Post #1404794

ChrisM@Work

Posted Wednesday, January 09, 2013 8:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

Composable dml:

INSERT INTO DataStaging_Archive (URN, DateProcessed, AnotherColumn, LastRemainingColumn)
	SELECT URN, DateProcessed, AnotherColumn, LastRemainingColumn
	FROM (
	DELETE TOP (@Batchsize) -- note: no order by, rows picked at random, see BOL
	FROM DataStaging ds
        OUTPUT
          deleted.URN,
          deleted.DateProcessed,
          deleted.AnotherColumn
          deleted.LastRemainingColumn
	) AS D

The whole statement is atomic: the INSERT and DELETE both succeed or both fail.

See http://www.sqlmag.com/article/sql-server/composable-dml

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1404839

ron.grace

Posted Thursday, January 10, 2013 7:14 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 28, 2013 3:32 AM
Points: 18, Visits: 50

I'm not particularly interested in the transaction logs anyway as I will always have the source data files to reload from if anything goes wrong, so is there any way of switching of logging completely.

Post #1405425

SQLSACT

Posted Thursday, January 10, 2013 8:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 1,191, Visits: 2,116

Maybe something like this

SET ROWCOUNT 20000
  DELETE from ....  
OUTPUT DELETED.* into ........
SET ROWCOUNT 0

Not sure about the running total

Post #1405475

wolfkillj

Posted Friday, January 11, 2013 9:12 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 721, Visits: 1,375

ron.grace (1/10/2013)

The only way, AFAIK, to "switch off" logging would be to change the recovery model of the database to bulk logged, which only enables minimal logging for certain bulk insert and insert operations rather than "switching it off" but also significantly affects the backup and restore options available to you for transactions that occur while the recovery model is set to bulk logged.

I'm curious about why you need a "staging archive" table, though. Since you'll always have the source files to reload if necessary, why move the staging data to an archive table (with all the concomitant workload on the server)?

Post #1406122

« Prev Topic | Next Topic »

Permissions