Data Pump

The Data Pump is a tool used to transfer data from one database to another and can be reached via Tools | Data Pump. The Data Pump tool that comes with Database Workbench supports several types of connectivity:

•	Supported database engine native (either directly through the client libraries or ODBC/ADO)

By using ODBC or ADO connectivity, you can transfer data to and from any other database engine.

As you can see in the above example, we have connected to a source database via ADO (1) and are connected to an InterBase database called 'finance-dest' (2). Both schemas are fetched (3 & 4) and one table mapping has been made (4).

Such a mapping is easily created by dragging the source table to the destination table and dropping it there. After such a drop, an attempt will be made to match column name - this matching process can be adjusted on the Options tab - it does a case insensitive match and filters any non-standard characters such as ' # $ _ etc...

Any columns that don't get matched will stay empty until you drag 'n drop a source column to the destination column.

The transfer order can be controlled via the UP/DOWN arrow buttons on the DataPump toolbar. This can be very useful if your target database has foreign key constraint defined.

Besides source database values, you can also choose to use a constant or expression of some kind that results in a valid value and uses valid SQL for that particular database engine. Do a context-mouse click on a column to enter such an expression.

In the above example, you can see that while transferring data, the destination column ACCOUNTS.ACT_ID will be filled by using a generator. Any expression that results in a valid SQL value is possible here.

For the source table, you can also add a custom SQL WHERE clause to filter data for the destination database. Do a right-mouse click to enter such a WHERE clause.

The above WHERE clause results in ENDBALL records being transferred with a value for SUMS above 10000.

You can also use a custom SQL statement as the source of data to transfer. Click the Create New Source SQL button in the toolbar and a simplified SQL Editor will be displayed.

In this SQL Editor, you can create and test your SQL statement. If you're satisfied with the results, close the editor to accept the changes. The SQL has to be "named" and you can enter a name for it.

If you want to delete it, simply use the context menu in the treeview and select Delete from the popup menu. Using a double click or Edit SQL from the menu will open up the editor again.

You can expand the SQL item just as you expand the tables and look at its columns.

Using an ODBC datasource requires a registered ODBC data source name. These can be created from the Windows Control Panel applet for ODBC datasources. This applet can also be reached via the 'Edit ODBC Datasources' button next to the ODBC Datasource drop down combo.

•	An ADO connection requires a quite complex connection-string. By clicking the 'Edit Connection string, a dialog will popup that lets you select from different ADO drivers and select your settings that will be passed to the ADO connection, see building an ADO connection string.

•	If you have tables & columns that use non-standard characters like spaces, select the SQL syntax that you want to use from the drop down box.

By default, the Data Pump will open an additional connection to your databases and optimize the running process for concurrency - that is, you will be able to continue working with Database Workbench. If you don't mind a stalled Database Workbench instance, click the arrow next to the Start Data Transfer Process and select Optimize for Speed from the menu.

1) If you need to convert data while transferring from one table to another, you can use a "custom SQL source" in a specific way.

Instead of using a simple (or complex) SQL statement to return results, you can also use a Stored Procedure that generates or converts data. Or you can use functions in your SQL to, for example, trim the contents of columns before doing the transfer.

2) Although you can use an ORDER BY clause on each table or view, this will still transfer all rows unless you specify a WHERE clause. But you cannot, for example, transfer the "first 100 rows". Again, you can easily use the "custom SQL source" to do this. Use a SELECT with a clause that trims the number of rows.