May 5, 2015 at 10:47 am
Hi SQL Gurus,
I am trying to delete thru small chunk from [AdventureWork].[Sales].[SalesOrderDetail]. I like to delete 10000 at a time where 'SalesOrderDetailID' > 50000. Another word I like to delete everything 'SalesOrderDetailID' greater than 50000 but in a loop.
I found a sample script (below) but couldn't modified enough to make it work. Is there better way to do it? Please share.
DELCARE @DeleteRowCnt INT
SET @DeleteRowCnt = 1
DECLARE @DeleteBatchsize INT
SET @DeleteBatchSize = 10000
WHILE (DeleteRowCnt > 0 )
BEGIN
DELETE TOP (@DeleteBatchsize) [dbo.].[Customer]
WHERE RegionCD = 'NorthAmerica'
sET @DeleteRowCnt = @@ROWCOUNT;
END
May 5, 2015 at 10:55 am
Dan121 (5/5/2015)
Hi SQL Gurus,I am trying to delete thru small chunk from [AdventureWork].[Sales].[SalesOrderDetail]. I like to delete 10000 at a time where 'SalesOrderDetailID' > 50000. Another word I like to delete everything 'SalesOrderDetailID' greater than 50000 but in a loop.
I found a sample script (below) but couldn't modified enough to make it work. Is there better way to do it? Please share.
DELCARE @DeleteRowCnt INT
SET @DeleteRowCnt = 1
DECLARE @DeleteBatchsize INT
SET @DeleteBatchSize = 10000
WHILE (DeleteRowCnt > 0 )
BEGIN
DELETE TOP (@DeleteBatchsize) [dbo.].[Customer]
WHERE RegionCD = 'NorthAmerica'
sET @DeleteRowCnt = @@ROWCOUNT;
END
Simple enough:
declare @DeleteBatchSize int = 10000;
while @DeleteBatchSize > 0
begin
delete top (@DeleteBatchSize) from [AdventureWork].[Sales].[SalesOrderDetail] where SalesOrderDetailID > 50000;
select @DeleteBatchSize = @@rowcount;
end
Now, why do you want to use a loop? What are you trying to accomplish? How much data is going to be deleted versus how much is going to be kept in the table? Is there data in other tables related to the data you are deleting? Does this data need to be deleted as well?
A loop is not always the best way to accomplish a task.
May 5, 2015 at 11:18 am
Thanks Lynn,
I was practicing delete million rows in small batch to prevent log size getting big.
May 5, 2015 at 11:27 am
Dan121 (5/5/2015)
Thanks Lynn,I was practicing delete million rows in small batch to prevent log size getting big.
Okay, then you also need to incorporate t-log backups if the database is not using the simple recovery model. Also, it also comes back to how many rows are being deleted versus how many are being kept? There may be a better way if you are actually retaining a small data set versus what you are deleting.
Also, be to sure to consider related data. If you are deleting detail rows, you may also want to delete parent data related to that data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply