How it works
The Data Transfer Manager retrieves data from a JDBC connection and saves the retrieved data to a second connection. You can retrieve data either by selecting a database object (table, view, etc.) or by entering a SQL query.
You have to do three steps for each transfer:
- Specify source (object or query) and destination (object, can be automatically created)
- Specify the field mapping
- Start Transfer
Start the Data Transfer Manager
The Data Transfer Manager can be started via the Tools -> Data Transfer Manager menu.
1. Specify Source and Destination Object
Click the "Connections" button in the Data Transfer Manager toolbar to display the source and destination objects.
Source definition
You can either select a connection/catalog/schema/object or you can specify your own SQL query to retrieve the source. Your SQL query does not have any limitations. This means you can join tables before transfer, order the result set, etc.
Destination definition
The destination is always a database object - either an existing or a new one. If you want to transfer your query results or table contents from your source object to an existing table uncheck the "Automatically create Table" option.
Automatically create Table
If the "Automatically create Table" option is checked the Data Transfer Manager creates a new table with the name specified in the Destination Table field based on the field definition of your source object.
You can use the Data Transfer Manager to copy tables between database servers from different vendors. As every database vendor supports several server specific data types in addition to the standard SQL data types, not all tables can be copied. Data Transfer Manager includes an intelligent mapping algorithm to find the best matching data type on a destination database server that does not support a specific data type in your source table or query.
Special note for Oracle users
Due to a limitation of the Oracle JDBC driver field length for varchar fields are not reported. This means if your source object is an Oracle table with varchar fields and you activate "Automatically create Table" the Data Transfer Manager has to "guess" the field lengths to create your destination table. Most likely you will get better results if you create the destination table manually and use the Data Transfer Manager to transfer data only.
2. Specify the Field Mapping
Click the "Mapping" button in the Data Transfer Manager toolbar to load the field list and build the default mapping. This has to be done once for each transfer.
The field mapping can convert date types or copy values directly to the specified destination columns. If you want to change the destination column double-click and overwrite the destination column name.
| Direct Copy |
|
The value from the source column is copied without modification to the destination column. |
| Don't Copy | The value from this source column is ignored. | |
| Date Conversion (Oracle to US ANSI) | If you want to copy date values from an Oracle database server to almost any other database server you need to activate the date conversion. Almost every database server (except Oracle) expects a date value in the form YEAR-MONTH-DAY. Oracle date values are converted in this form during transfer. | |
| Date Conversion (ANSI to Oracle US) | If you want to copy data values from a non-Oracle database server to an Oracle database server the date values have to be converted in the Oracle specific date format with this option. |
Handling server specific conventions
Database servers from different vendors do not only have different server specific data types, but they additionally specify data in a different way. In addition to the date conversions automatic conversions take place to adopt to differences in numeric and alphanumeric data types.
3. Start the Data Transfer
Click the "Transfer" button to start the data transfer. If your data types do not match you will get an error message window.