Use a separate TIB_DSQL component for those statements.

I recommend that you use a stored procedure that takes the records and
attempts an insert and if it fails by key violation then do an update. Prior
to starting the routine, increment a generator and have the generator set to
all records that are brought in from the source. Once it is all complete,
delete all records that have a generator column value less than the latest
one from the sync.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Michael L. Horne" <guardian@...>
To: <IBObjects@yahoogroups.com>
Sent: Saturday, February 10, 2001 9:22 PM
Subject: [IBO] Using InsertSQL, EditSQL, DeleteSQL


> Hello all
>
> I have 2 DBs where I need to merge the data from DB1 to DB2. I
> don't really care what is in the records currently, but am not
> able to clear the data before hand and then do a complete insert.
>
> So I thought about doing a Select of the IDs in the TO table and
> comparing them to the IDs of the From table. If they match then
> Update the data of the record, if not there then do an Insert or
> a Delete, depending on file.
>
> I understand that I can setup InsertSQL, EditSQL, DeleteSQL within
> a single Cursor. Such as follows:
>
> // Setup the Select
> CursorSelect.SQL.Clear;
> CursorSelect.IB_Connection := cnTo;
> CursorSelect.SQL.Add('Select '+sID+' from '+sFileName);
> CursorSelect.SQL.Add(' Order by '+sID);
>
> // Setup Insert SQL
> CursorSelect.InsertSQL.Clear;
> CursorSelect.InsertSQL.Add('Insert into Cat (CAT_ID, CAT_PART,
> CAT_DESCR)');
> CursorSelect.InsertSQL.Add(' Values (:CAT_ID, :CAT_PART, :CAT_DESCR)');
>
> // Setup Update SQL
> CursorSelect.EditSql.Clear;
> CursorSelect.EditSql.add('Update Cat set CAT_ID = :CAT_ID, ');
> CursorSelect.EditSql.add('CAT_PART = :CAT_PART, CAT_DESCR =
:CAT_DESCR');
> CursorSelect.EditSql.add(' where '+sID+' = :'+sID);
>
> // Setup Delete SQL
> CursorSelect.DeleteSql.Clear;
> CursorSelect.DeleteSql.Text('Delete from '+sFileName);
> CursorSelect.DeleteSql.Add(' where '+sID+' = :'+sID);
>
> Now to the question, after I start looking at the records, I assume
> I put the data into the cursor with commands like:
>
> CursorSelect.ParamByName('Cat_Part').AsString :=
> SourceSelect.ParamByName('CatPart').AsString;
>
> Ok, now the data is ready to send, How do I actually do a INSERT, or
> a UPDATE, or DELETE?
>
> Do I do a
> CursorSelect.Edit;
> .... set the fields
> CursorSelect.Post;
> Something like this?
>
> Thanks
> Michael L. Horne
>
>
>
>
>
>