deleting 11 000 000 rows...

Hi!
I just dicovered that one of our tables contains over 11 000 000 rows.
My problem right now is to delete a major part of these rows.
What I've done so far is to create a sp that handles this, but no matter how I do, the batch gets sent with all delete:s at the same time.

What I understand, the batch gets sent after the GO-statement, and at the same time, all my variables looses scope...

The data is organized on dates, one date may comprise several rows, and I know that one batch can handle all rows in one date...

The table is indexed but has no PK

The main SP:

CREATE PROCEDURE clear_lagertransaktioner
@.maxDate SMALLDATETIME
AS
DECLARE @.date SMALLDATETIME
DECLARE dateCur CURSOR FOR select distinct date from lagertransaktioner
OPEN dateCur

FETCH NEXT FROM dateCur INTO @.date
WHILE @.@.FETCH_STATUS = 0 AND @.date<@.maxDate
BEGIN

--Call another SP that I hoped would send the batch
exec del_post_lagertransaktioner @.date
FETCH NEXT FROM dateCur INTO @.date

END

CLOSE dateCur
DEALLOCATE dateCur
GO


The nested SP:
CREATE PROCEDURE del_post_lagertransaktioner
@.d SMALLDATETIME
AS

DELETE FROM lagertransaktioner WHERE date=@.d
GO

Does any of you have a better idea, cause this doesn't work.Mabye it is better for me to just insert needed data into a new table and drop the original table, since this is a once-in-a-lifetime situation (hopefully)

What do you say?

/Bix|||Two options.

Run SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp) and grab your small amount of rows and insert them into a new table. Then DROP the old table and re-name the new one. Provided you have an index to select from that should provide the lowest I/O.

If you want to batch it, do something like this:

SET ROWCOUNT 10000
WHILE @.@.ROWCOUNT > 0
DELETE FROM table_name WHERE etc.

That'll delete 10,000 of them at a time, for example.

You could also re-write the DELETE to use a subquery that uses TOP 10000 to also achive this effect.