10.12.07
Deleting huge amounts of records
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