11. Copy data across databases

11.1. General parameters for the WbCopy command.
11.2. Copying data from one or more tables
11.3. Copying data based on a SQL query
11.4. Update mode
11.5. Synchronizing tables
11.6. Examples

The WbCopy is essentially the command line version of the the DataPumper. For a more detailed explanation of the copy process, please refer to that section. It bascially chains a WbExport and a WbImport statement without the need of an intermediate data file. The WbCopy command requires that a connection to the source and target database can be made at the same time.

11.1. General parameters for the WbCopy command.

ParameterDescription
-sourceProfile

The name of the connection profile to use as the source connection. If -sourceprofile is not specified, the current connection is used as the source.

If the profile name contains spaces or dashes, it has to be quoted.

-sourceGroup

If the name of your source profile is not unique across all profiles, you will need to specify the group in which the profile is located with this parameter.

If the group name contains spaces or dashes, it has to be quoted.

-targetProfile

The name of the connection profile to use as the target connection. If -targetprofile is not specified, the current connection is used as the target.

If the profile name contains spaces or dashes, it has to be quoted.

-targetGroup

If the name of your target profile is not unique across all profiles, you will need to specify the group in which the profile is located with this parameter.

If the group name contains spaces or dashes, it has to be quoted.

-commitEvery The number of rows after which a commit is sent to the target database. This parameter is ignored if JDBC batching (-batchSize) is used.
-deleteTarget

Possible values: true, false

If this parameter is set to true, all rows are deleted from the target table before copying the data.

-mode

Defines how the data should be sent to the database. Possible values are INSERT, UPDATE, 'INSERT,UPDATE' and 'UPDATE,INSERT'. Please refer to the description of the WbImport command for details on.

-syncDelete

If this option is enabled -syncDelete=true, SQL Workbench/J will check each row from the target table if it's present in the source table. Rows in the target table that are not present in the source will be deleted. As this is implemented by checking each row individually in the source table, this can take some time for large tables. This option requires that each table in question has a primary key defined.

Combined with an UPDATE,INSERT or UPDATE,INSERT mode this creates an exact copy of the source table.

If more than one table is copied, the delete process is started after all inserts and updates have been processed. It is recommended to use the -checkDependencies parameter to make sure the deletes are processed in the correct order (which is most probably already needed to process inserts correctly).

To only generate the SQL statements that would synchronize two databases, you can use the command WbDataDiff

-keyColumns

Defines the key columns for the target table. This parameter is only necessary if import is running in UPDATE mode. It is ignored when specifying more than one table with the -sourceTable argument. In that case each table must have a primary key.

-batchSize

Enable the use of the JDBC batch update feature, by setting the size of the batch queue. Any value greater than 1 will enable batch modee. If the JDBC driver supports this, the INSERT (or UPDATE) performance can be increased.

This parameter will be ignored if the driver does not support batch updates or if the mode is not UPDATE or INSERT (i.e. if -mode=update,insert or -mode=insert,update is used).

-commitBatch

Valid values: true, false

When using the -batchSiez parameter, the -commitEvery is ignored (as not all JDBC drivers support a COMMIT inside a JDBC batch operation. When using -commitBatch=true SQL Workbench/J will send a COMMIT to the database server after each JDBC batch is sent to the server.

-continueOnError

Defines the behaviour if an error occurs in one of the statements. If this is set to true the copy process will continue even if one statement fails. If set to false the copy process will be halted on the first error. The default value is false.

With PostgreSQL continueOnError will only work, if the use of savepoints is enabled. This can be done using by setting the property workbench.db.postgresql.import.usesavepoint=true in in the configuration file workbench.settings. If this is enabled, then each INSERT (or UPDATE) statement will be "wrapped" between savepoints so that a statement error can be recovered.

-showProgress

Valid values: true, false, <numeric value>

Control the update frequence in the statusbar (when running in GUI mode). The default is every 10th row is reported. To disable the display of the progress specifiy a value of 0 (zero) or the value false. true will set the progress interval to 1 (one).

11.2. Copying data from one or more tables

ParameterDescription
-sourceTable The name of the table(s) to be copied. You can either specifiy a list of tables: -sourceTable=table1,table2. Or select the tables using a wildcard: -sourceTable=* will copy all tables accessible to the user. If more than one table is specified using this parameter, the -targetTable parameter is ignored.
-checkDependencies

When copying more than one file into tables with foreign key constraints, this switch can be used to import the files in the correct order (child tables first). When -checkDependencies=true is passed, SQL Workbench/J will check the foreign key dependencies for the tables specified with -sourceTable

-sourceWhere A WHERE condition that is applied to the source table.
-targetTable The name of the table into which the data should be written. This parameter is ignored if more than one table is copied.
-createTargetIf this parameter is set to true the target table will be created, if it doesn't exist. Valid values are true or false.
-dropTarget If this parameter is set to true the target table will be dropped before it is create. This parameter is ignored if -createtarget=true is specified.
-columns

Defines the columns to be copied. If this parameter is not specified, then all matching columns are copied from source to target. Matching is done on name and data type. You can either specify a list of columns or a column mapping.

When supplying a list of columns, the data from each column in the source table will be copied into the corresponding (i.e. one with the same name) column in the target table. If -createtarget=true, then the list also defines the columns of the target table. The names have to be separated by comma: -columns=firstname, lastname, zipcode

A column mapping defines which column from the source table maps to which column of the target table (if the column names do not match) If -createtable=true then the target table will be created from the specified target names: -columns=firstname/surname, lastname/name, zipcode/zip Will copy the column firstname from the source table to a column named surname in the target table, and so on.

This parameter is ignored if more than one table is copied.

-preTableStatement

This parameter defines a SQL statement that should be executed before the copy process starts inserting data into the target table. The name of the current table (when e.g. importing a whole directory) can be referenced using ${table.name}.

To define a statement that should be executed after all rows have been inserted but before the data is committed, you can use the -postTableStatement parameter.

These parameters can e.g. be used to enable identity insert for MS SQL Server:

-preTableStatement="set identity_insert ${table.name} on"
-postTableStatement="set identity_insert ${table.name} off"
Errors resulting from executing these statements will be ignored. If you want to abort the copy in that case you can specify -ignorePrePostErrors=false and -continueOnError=false.

11.3. Copying data based on a SQL query

ParameterDescription
-sourcequeryThe SQL query to be used as the source data (instead of a table).
-columnsThe list of columns of the target table, in the order in which they appear in the source table.

11.4. Update mode

The WbCopy command understands the same update mode parameter as the WbImport command. For a discussion on the different update modes, please refer to the WbImport command.

11.5. Synchronizing tables

Using -mode=update,insert ensures that all rows that are present in the source table do exist in the target table and that all values for non-key columns are identical.

When you need to keep two tables completely in sync, rows that are present in the target table that do not exist in the source table need to be deleted. This is what the parameter -syncDelete is for. If this is enabled (-syncDelete=true) then SQL Workbench/J will check every row from the target table if it is present in the source table. This check is based on the primary keys of the target table and assumes that the source table as the same primary key.

Testing if each row in the target table exists in the source table is a substantial overhead, so you should enable this option only when really needed. DELETEs in the target table are batched according to the -batchSize setting of the WbCopy command. To increase performance, you should enable batching for the whole process.

Internally the rows from the source table are checked in chunks, which means that SQL Workbench/J will generate a SELECT statement that contains a WHERE condition for each row retrieved from the target table. The default chunk size is relatively small to avoid problems with large SQL statements. This approach was taken to minimize the number of statements sent to the server.

The automatic fallback from update,insert or insert,update mode to insert mode applies for synchronizing tables using WbCopy as well.

11.6. Examples

11.6.1. Copy one table to another

WbCopy -sourceProfile=ProfileA
       -targetProfile=ProfileB
       -sourceTable=the_table
       -targetTable=the_other_table;

11.6.2. Synchronize the tables between two databases

This example will copy the data from the tables in the source database to the corresponding tables in the target database. Rows that are not available in the source tables are deleted from the target tables.

WbCopy -sourceProfile=ProfileA
       -targetProfile=ProfileB
       -sourceTable=*
       -mode=update,insert
       -syncDelete=true;

11.6.3. Copy only selected rows

WbCopy -sourceProfile=ProfileA
       -targetProfile=ProfileB
       -sourceTable=the_table
       -sourceWhere="lastname LIKE 'D%'
       -targetTable=the_other_table;

11.6.4. Copy data between tables with different columns

This example copies only selected columns from the source table. The column names in the tables do not match and a column mapping is defined. Before the copy is started all rows are deleted from the target table.

WbCopy -sourceProfile=ProfileA
       -targetProfile=ProfileB
       -sourceTable=person
       -targetTable=contact
       -deleteTarget=true
       -columns=firstname/surname, lastname/name, birthday/bday;

11.6.5. Copy data based on a SQL query

When using a query as the source for the WbCopy command, the columns from the query need to be matched to the columns of the target table.

WbCopy -sourceProfile=ProfileA
       -targetProfile=ProfileB
       -sourceQuery="SELECT firstname, lastname, birthday FROM person"
       -targetTable=contact
       -deleteTarget=true
       -columns=surname, name, bday;

The order in the -columns parameter must match the order in the SELECT statement!