Re: compare 2 recordsets


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
news:OwWCChmAIHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
Yes, 2 databases, so I can't do it in SQL.

Why not?

Every database I know has a method of registering a 3rd party database with
itself.
For SQL Server, it is stored producedure

sp_addlinkedserver

with arguments
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]

After that I could do

DELETE FROM tablename where somefield IN (SELECT somefield FROM
remoteserver.dbo.anothertablename)
Far quicker than the 2-recordset approach

But on the 2-recordset approach:

It is not a double loop.
It is a single loop anti-merge (and very fast).
Something like

===========================

IF not recA.EOF and not recB.EOF THEN
DO
If recA.Item.Fld1 < rec2.Item.FldB THEN
recA.Delete
recA.MoveNext
If recA.EOF THEN EXIT DO
ELSEIf recA.Item.Fld1 > recB.Item.Fld1 THEN
recB.MoveNext
If recB.EOF THEN EXIT DO
ELSE
' they are equal
recA.MoveNext
recB.MoveNext
If recA.EOF or recB.EOF THEN EXIT DO
END IF
LOOP
END IF

'Deal with the fact that recA may not be exhausted but recB must be (other
case needs no consideration)
DO WHILE NOT recA.EOF
recA.Delete
recA.MoveNext
LOOP

=================================

Note you did not say if recA or recB could have repeated values
If they can I would change

ELSE
' they are equal
recA.MoveNext
recB.MoveNext
If recA.EOF or recB.EOF THEN EXIT DO
END IF

to

ELSE
' they are equal
recA.MoveNext
If recA.EOF THEN EXIT DO
END IF

so that all A's duplicate values match the B record

Stephen Howe


.