Mass deleting records from an ADO recordset

[kabirpatel]

Hi all,

I have an ADO recordset that contains about 100,000 records.

Is it possible to delete the first 60,000 records from this recordset using one command?

At the moment I am using the following:

For intX = 1 To 60000
rs.Delete adAffectCurrent
rs.MoveNext
Next

The above is very slow so I need an alternative.

Thanks in advance
Kabir

 

[CaptainD]

Why not do it with a SQL statement?

strSQL =  "DELETE YourTable WHERE YourTable.TableID IN (SELECT TOP 600000 YourTable.TableID FROM YourTable)"

 

[kabirpatel]

I am trying to export data from SQL server to Excel via my VB interface.

I execute my stored procedure and it returns 100,000 records. Since Excel only allows 65535 records per worksheet I export the first 60,000 records from my recordset and then want to export the remaining 40,000 in the second worksheet.

To do this I need to delete the first 60,000 records.

Cheers,
Kabir

 

[CaptainD]

Actually you should be able to do it without deleteing records by using "TOP" and "NOT IN" in your SQL statment

Something like This:

'First Recordset
"SELECT TOP 60000 from YourTable ORDER BY YourTable.TableID"

'Second Recordset (Alter to pick your fields)
"SELECT * FROM YourTable WHERE yourTable.TableID NOT IN (SELECT TOP 60000 FROM YourTable ORDER BY YourTable.TableID)"

 

[kabirpatel]

The problem with your solution is that in the future my recordset might increase significantly in size (to say 200,000 records) in which case I would need 4 recordsets.

I would much rather use one SQL stored proc to return all the data I need and then tackle 60,000 records at a time.

Cheers,
Kabir

 

[RoyVidar]

What method are you using to "export" the records?

If you use the copyfromrecordset method of the excel range object, you can specify how many records you wish to copy - you'll probably need something a bit more dynamic than the below, though ;-)

set sh = wr.sheets(1)
sh.range("a1").copyfromrecordset rs, 60000
set sh = wr.sheets(2)
sh.range("a1").copyfromrecordset rs, 60000
...



Roy-Vidar

 

[johnwm]

A slight modification to CaptainD's suggestion will solve the problem. Use his 'Select' statement for your first recordset and move that into Excel. Then use the same selection criteria in a Delete statement in SQL.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[CaptainD]

Here is a sample stored procedure I created that might you might want to try as well(To show you another method). What it does is creates a temporary table in memory (assuming you are using SQL Server 2000 or above) that will be dropped automatically from memory once the procedure runs. In this case I'm populating with number but you would do this with your data. You pass it the start value and end value and it will take that block of data. your VB program would pass those with different numbers each time it changes work sheets.

I ran it through query analyzer and it works as expected.

CREATE PROCEDURE DBO.uspTest
(
	@iStart INT = NULL,
	@iEnd INT = NULL
)

 AS
SET NOCOUNT ON

	DECLARE @tTempTable TABLE (iID INT IDENTITY(1,1),
					iTestNumber INT)
	DECLARE @iValue INT
	SET @iValue = 0
	 WHILE @iValue <> 20
BEGIN
		SET @iValue = @iValue + 1
		INSERT INTO @tTempTable (iTestNumber) Values(@iValue)
END

SELECT * FROM @tTempTable WHERE iID >=@iStart AND iID <=@iEnd
GO