Transaction-like behaviour with COPY QUERY
rdnielsen The documentation of the COPY, COPY QUERY, and IMPORT metacommands has been modified to, perhaps, be a little clearer.
The commit that is automatically done by default by these metacommands is subject to the AUTOCOMMIT setting, but is not turned off by BEGIN BATCH.
Therefore, to perform multiple COPY operations and not commit any of them unless they all succeed, the approach should be to: 1) Set AUTOCOMMIT OFF for each database that is the target of a copy command; 2. Run the COPY metacommands; 3. After END BATCH, set AUTOCOMMIT ON WITH COMMIT for each database to which data were copied. If there is an error during copying, step 3 will not be executed, and a rollback will be performed on all databases when execsql exits.
A BATCH could be used within the AUTOCOMMIT OFF/ON statements, but it is unnecessary when AUTOCOMMIT is used for every individual database. AUTOCOMMIT is database-specific, whereas the BEGIN BATCH and END BATCH metacommands will turn off/on committing of SQL statements for all databases. The BATCH metacommands don't affect commits that may be made by COPY and IMPORT metacommands--only the AUTOCOMMIT metacommand affects those.
Thank you very much, this is much clearer now!
rdnielsen 
Fork
Hi,
I'm attempting to copy data between two different databases. The system works fine:
-- !x! CONNECT TO SQLSERVER(...) as output_db -- !x! COPY QUERY <<!!query1!!>> FROM input_db TO DEST_TABLE IN output_db -- !x! COPY QUERY <<!!query2!!>> FROM input_db TO OTHER_DEST_TABLE IN output_db -- !x! DISCONNECT output_db -- !x! DISCONNECT input_db
However, I'm struggling to understand from the docs how to model a Transaction-like behaviour: if any COPY QUERY command fails, I'd love no data at all to be written to the destination database (e.g. rollback if the transaction fails).
By reading the docs it seems I should do the following:
- set AUTOCOMMIT to off - Wrap the block above in a BEGIN BATCH/END BATCH
However the docs for COPY QUERY report the following: "The data addition to the target table is always committed. Therefore, the COPY QUERY metacommand generally should not be used within transactions or BATCHes."
Does that mean transaction-like behaviours are not supported?