Delete every time 1000 records in while loop.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete every time 1000 records in while loop.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-18 : 10:04:00

Dear All,

In my SQL statement i am having 10000 records, i want to delete 1000 records every time and commit them.
Can anybody tell me the code for this ?
I am trying following code but in DELETE statement how can i pass only 1000 records from the select statement.

SELECT ID FROM tb_no_ra_sh WHERE W_ID = @WID
While(@Count<=@@ROWCOUNT)
Begin
WHILE(@Count <= @CountThousand)
BEGIN
PRINT 'Start deleting rows' + @WID;
DELETE FROM tb_no_ra_sh WHERE W_ID = @WID;
SET @Count = @Count + 1;
END
COMMIT TRANSACTION;
SET @CountThousand = @CountThousand + 1000;
End

Thanks and Ragard's
Harish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 10:07:30

use


DELETE TOP (1000)....
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-18 : 10:10:50

Hello Friend,

Can you please check my code.

Thnks,
Harish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 10:11:39

why do you need to use while loop?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-18 : 10:15:05

begin tran
delete top (1000) ...
commit tran
GO 10

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlmaster555112
Starting Member

13 Posts

Posted - 2011-10-18 : 11:23:44

I agree with webfred why make things confusing if we can solve them via simple stuff,you can use
DELETE FROM Table WHERE ID NOT IN (SELECT id FROM (SELECT TOP 1000 ID FROM Table) AS x)
or else instead if you wanna delete any random values try
instead of round(Rand()*1000,0) instead of TOP 1000 in above query.

@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

Kristen
Test

22859 Posts

Posted - 2011-10-18 : 11:56:17

If you are in a loop, and want to delete (say) 1,000 rows each iteration from a large table, then you need a more efficient method than DELETE TOP 1000

DELETE TOP 1000 ... will require that the selection is re-made each time around the loop, which will be the slowest part of the process (i.e. the Select will be slower than the Delete). You should, probably, try to delete 1,000 rows that are clustered close to each other on the clustered-index.

The best solution I have found is to pre-select all the PK's for deletion into a #temp table, with an IDENTITY column, ordered by the PKey, and then to delete by joining the #Temp table to the main table - which you can then do using a range on the Identity column, incremented each iteration of the loop.

You can use WAITFOR to pause for a few seconds on each loop iteration, to reduce impact for other concurrent users, and to force a TLog backups every N,000,000 deletions to prevent the LDF file being extended unnecessarily.

I've got a post with sample code somewhere here, which I can find if you cannot locate it easily.

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-10-18 : 12:04:05

Thnks All, all this information is useful for me.

Thnks,
Harish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources