Hello

I have a method that uses TEDBQuery to move records that are over a (variable) age to an archive, selected by..

'SELECT RecdID FROM "TblReg" WHERE (EventDate < :DFrom)'

The method loops through the result set, and copies the row to the archive table...

INSERT INTO TblArc (RecdID, ClientID, EventCode, EventDate, Note, ArchiveDT)
 (SELECT RecdID, ClientID, EventDate, Note, Current_TimeStamp
  FROM TblReg WHERE TblReg.RecdID = :RecdID)

If there is a 1004 error (duplicate index) it swallows the error, as somehow the record already exists in the archive, then deletes the the record in
TblReg. That is why I prefer to loop through the result set, as sometimes one record amongst the set is already archived.

I have tried to put that into a script, with the added feature of first checking to see if that RecdID exists in TblArc, but I have not been able to get it
to work.

How should this method be laid out, in script form? I am new to declaring cursors and the like, so be gentle.

Regards & TIA

Peter

Peter,

> I have tried to put that into a script, with the added feature of first checking to see if that RecdID exists in TblArc, but I have not been able to get it
> to work.
> How should this method be laid out, in script form? I am new to declaring cursors and the like, so be gentle.

You can do it in one statement:

INSERT INTO TblArc (RecdID, ClientID, EventCode, EventDate, Note, ArchiveDT)
   (SELECT RecdID, ClientID, EventDate, Note, Current_TimeStamp
    FROM TblReg WHERE EventDate < :DFrom AND RecdID not in (SELECT
RecdID from TblArc))

In order to delete duplicate records just use:

DELETE FROM TblReg where RecdID in (select RecdID from TblArc)

Regards Uli

Uli

Of course - why didn't I think of that?

Thanks, again

Regards

Peter