10.12.07

Deleting huge amounts of records

Posted in DBA, Microsoft Sql server, TSQL at 11:41 am by

When you have to delete a whole bunch of records from a table which has to stay in use. Then you could do it with a cursor. but you can also do it with the following approach. Use top to limit the amount of records deleted and put the deletion in a loop.

Sample code: 

DECLARE @p as int;

DELETE [dbo].[Klanthistoriek]
FROM (SELECT TOP 50000 klh_id
FROM
[GKA].[dbo].[Klanthistoriek]
WHERE
KLH_CreatieDatum between ’2007-09-18 10:47:40.820′ and ’2007-09-20 15:29:32.663′ ) as temptable
Where [dbo].[Klanthistoriek].klh_id = temptable.klh_id;

SET @p= @@rowcount;
WHILE @p <> 0
BEGIN
DELETE [dbo].[Klanthistoriek]
FROM (SELECT TOP 50000 klh_id
FROM
[GKA].[dbo].[Klanthistoriek]
WHERE
KLH_CreatieDatum between ’2007-09-18 10:47:40.820′ and ’2007-09-20 15:29:32.663′ ) as temptable
Where [dbo].[Klanthistoriek].klh_id = temptable.klh_id;

SET @p = @@rowcount;
PRINT @p;
END

Comments are closed.