Bulk delete in MS-SQL server

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.

Leave a Reply

Your email address will not be published.