Salvatore Saieva (saieva@dpg.rnb.com) wrote:
: My need is to update a remote database each day. I would like to export
: the data from my local database to ASCII flat files using the SQL unload
: statement and then make it available for downloading (using Zmodem,
: Kermit, or some other modem protocol) by the remote site.
: At the opposite end a script will run which will use a SQL load statement
: to load the data into the remote database. My problem is that some of the
: records that are exported from my local database will update existing records
: in the remote database. The SQL load command only allows an insert clause
: for records being loaded from a flat file.
Sal,
do it the following way:
1) create a temporary table that has the same structure as the target
table.
2) load the ASCII file into that temp table
3) With an ESQL/C program, open a cursor on that temp table that selects
all records.
4) For each record, try an INSERT into the target table. If the INSERT
works (sqlca.sqlcode == 0) then all is well. If it fails with
sqlca.sqlcode == -239 (could not insert new row - duplicate value
in a UNIQUE INDEX column) that means that the row is already there
and you have to do an UPDATE of that row with the values from the
temp table.
Of course, you have to have an unique index on your primary key,
otherwise this approach won't work.
We have been using this approach successfully for several applications.
We haven't been able to find a way to do this in pure SQL, since there
are no error handling capabilities.
Hope this helps,
Richard
--
+----------------------------+-------------------------------------------+
| Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de |
| EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3421 |
| Klinikum Grosshadern | FAX : +49-89-7095-8886 |
| 81366 Munich, Germany | |
+----------------------------+-------------------------------------------+