Subject Re: [IBO] Using InsertSQL, EditSQL, DeleteSQL
Author Helen Borrie
At 11:22 PM 10-02-01 -0500, you wrote:
>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.

Why can't you pass a DSQL as:



Then surely it is a simple datapump operation - or don't I understand what you want to do?


>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,
> 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
>Do I do a
> CursorSelect.Edit;
> .... set the fields
> CursorSelect.Post;
>Something like this?
>Michael L. Horne

All for Open and Open for All
InterBase Developer Initiative ยท