Database Data Transfer Utility

The averSQL data transfer utility let’s you easily and quick transfer data from one database to another. You can setup a transfer for one table or you can create a process that would transfer data from many tables. Once the setup is complete, all it takes to complete the transfer is the click of one button. This feature is available for SQL Anywhere, SAP ASE, and SQL Server. You can transfer data between the same DBMS or each of the aforementioned.

maintoolbar

The main toolbar contains all your options to get you started:

  • Transfer All
    • The transfer all button will loop through all the added items and transfer data as defined within that item. Once complete, a transfer report will display.
    • You’ll need to add new tables to your list before this button becomes useful. See Add New Table for more information.
  • Save
    • The save button stores all changes to the transfer script into a file. You can also use the File\Save menu or the Save button on the applications main toolbar.
    • Use this so that you can open it later and perform your data transfer over and over again.
  • Add New Table
    • Clicking this button inserts a new item into the transfer window. You will be prompted to enter a name for the item (suggestion would be to give it the same name as the table you want to transfer data from).
    • After entering a name, a new blank transfer window will display.
  • Delete Selected Table
    • This button simply removes an item from the transfer window.
  • Disable\Enable Table
    • You can disable an item in the transfer window with this button. When the transfer is run, any disabled items will be passed over.
  • Move Up\Move Down
    • The move up and down buttons changes the vertical position of an item (i.e. the order in which the transfer takes place).

transfertable

After you’ve added a table, you need to instruct the data transfer utility on how to perform the transfer. Double click on an item in the main transfer list. A new tab will open. Fill in the source and target data source information.

After a table is selected from the table drop down, all columns will be loaded into the column grid. Here, columns can be removed and their position moved. This gives you the flexibility to match a source table to a target table when the structure is not the same. You can also add “static” values to the source column list so that, for example, a date column in the source always has a specific date or a char column always has a specific character value.

There are several transfer modes:

  1. Delete\Insert
    • All rows from the target database will be deleted. Each row from the source database will be inserted into the target.
  2. Update\Insert
    • All rows from the target will be compared to the source (based on matching primary keys). If a row from the source database is found in the target, the target will be updated. If a row from the source database is not found in the target, the row will be inserted into the target.
  3. Insert Only
    • Only rows from the source that do not exist in the target will be transferred.
  4. Update Only
    • Only rows from the source that exist in the target will be updated.

To restrict the rows that are moved from the source to the target, you can declare a where clause. This WHERE will be applied to the source before the source data is retrieved. You can see how many rows will be retrieved from the source by clicking the validate button.

You can transfer data for a single table by opening it (double clicking in the list) and clicking the execute button on the toolbar within the opened tab. A transfer report will display for that one item.

You can drag an item from the Object Browser onto any of the drop downs to automatically load all the data source, database and object name information.

Note: To facilitate the transfer of information from two completely different data sources, the client needs to act as the intermediary. What this means is that all applicable data is retrieved from the source and all applicable data is retrieved from the target. In the event of a Delete\Insert, no data is retrieved from the target, just the source. Keep this in mind because moving millions of rows of data can tax your available memory and possibly take a long time to finish.

Note: When applicable, Identity fields are disabled while inserting data. This is done to preserve relationships between tables where one table contains a foreign key to another tables Identity column. If you’re transfering data between tables that contain identity columns, it may be best to use the Delete\Insert option. Using Update\Insert may result in undesirable updates.

Note: Before data is transfered the foreign keys for the table are disabled. The process of deleting, inserting and updating may not satisfy foreign key constraints until data from other tables are present.

Warning: It’s always a good idea to backup the target database prior to initiating the transfer.