Moving data between databases

You can use the Data Migration Wizard to migrate, or move, data between desktop database tables and SQL database servers. The wizard moves the actual data, as well as the database schema, from one database to another. Both source and target databases can be either desktop database tables or SQL tables. The Data Migration Wizard only works with databases for which SQL Links are available. You must have the Borland Database Engine (BDE) and the appropriate SQL Links installed.

To use the Data Migration Wizard,

  1. Create an alias for both the source and target databases using the Borland Database Engine (BDE) Configuration Utility or select Tools|SQL Explorer from the menu. If using the BDE Configuration Utility, follow the directions for creating an alias. Select the driver for the source or target database, and set all the parameters. For information about creating aliases, refer to the BDE Configuration Utility online help files. If using the SQL Explorer, refer to its online help for information.

  2. Select Wizard|Data Migration Wizard from the menu. In the first screen of the Data Migration Wizard, select the alias or a directory for the source database for the data to be moved from. Desktop databases allow you to select either an alias or a directory; however, SQL server databases always require an alias and may require a login.

  3. Select the alias for the target database for the data to be moved to in the second screen of the Data Migration Wizard.

  4. Select the tables that you want to move from the source database to the target database by moving them from the Available Tables list to the Selected Tables list.

  5. Modify any data types, indexes, or referential integrity that are not supported on the target database.

  6. Upsize the data.

  7. View the final status report to determine the sequence in which data objects have been upsized, what data objects were upsized, and how they appear on the target. You can now update or modify the data directly on the target.

Before you move any data, you must understand how the data was created on the source database and how it will appear on the target database. Depending upon the structure of your data on the source database and the database objects that are supported on the target database, some modifications may be required. For example, some servers do not allow spaces in field names, some do not allow words like "Date" to be a field name. Referential integrity requires that both tables in the referential integrity relationship be moved to the target database.

Note: When moving data to a Microsoft SQL Server, be sure to set the configuration parameters MAX QUERY TIME and TIMEOUT to 20. This ensures data movement.

Note: When moving data to a Paradox table, any indexes from the source relation will not be transferred. This is due to the design of Paradox tables, requiring a primary index prior to creating secondary indexes.