If you want to simulate bulk-deleting in Microsoft SQL-server this can do the trick. I use it to avoid getting a transaction log that fills up the harddisk.
The example uses a stored procedure, but you don’t have to do this. The stored procedure is given a year-variable that is used in the SQL-sentences that selects what has to be deleted.
USE [YOUR_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_BULK_DELETE_AREA_YEAR @ActionYear char(4)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
DECLARE @Count INT
Declare @for_delete INT
Declare @chunk_size INT
SELECT @chunk_size=100000
SELECT @Count = 0
select @for_delete=count(*) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
While (@Count < @for_delete)
BEGIN
SELECT @Count = @Count + @chunk_size
BEGIN TRAN
DELETE top(@chunk_size) from [YOUR_TABLE] where [YOUR_VARIABLE] in (@ActionYear)
COMMIT TRAN
END
END
GO
It’s excecuted in the following manner: exec sp_BULK_DELETE_AREA_YEAR ‘2011’
I haven’t figured out how to use a variable that contains the tablename. So I don’t think it’s possible. You have to write the table name.
The solution was found here.
