Create a target database.

From Interactive SQL, connect to the target database.

Create a remote server to connect to the remote database.

Create an external login to connect to the remote database. This is only required when the user has different passwords on the target and remote databases, or when you want to log in using a different user ID on the remote database than the one you are using on the target database.

Create a local user who will own the migrated tables in the target database.

In the SQL Statements pane, run the sa_migrate system procedure. For example:

CALL sa_migrate( 'local_user1', 'rmt_server1', NULL, 'remote_user1', NULL, 1, 1, 1 );

This procedure calls several procedures in turn and migrates all the remote tables belonging to the user remote_user1 using the specified criteria.

Create a target database.

From Interactive SQL, connect to the target database.

Create a remote server to connect to the remote database.

Create an external login to connect to the remote database. This is only required when the user has different passwords on the target and remote databases, or when you want to log in using a different user ID on the remote database than the one you are using on the target database.

Create a local user who will own the migrated tables in the target database.

Run the sa_migrate_create_remote_table_list system procedure. For example:

CALL sa_migrate_create_remote_table_list( 'rmt_server1',
     NULL, 'remote_user1', 'mydb' );

You must specify a database name for Adaptive Server Enterprise and Microsoft SQL Server databases.

This populates the dbo.migrate_remote_table_list table with a list of remote tables to migrate. You can delete rows from this table for remote tables that you do not want to migrate.

Run the sa_migrate_create_tables system procedure. For example:

CALL sa_migrate_create_tables( 'local_user1' );

This procedure takes the list of remote tables from dbo.migrate_remote_table_list and creates a proxy table and a base table for each remote table listed. This procedure also creates all primary key indexes for the migrated tables.

If you want to migrate the data from the remote tables into the base tables on the target database, run the sa_migrate_data system procedure. For example:

Execute the following statement:

CALL sa_migrate_data( 'local_user1' );

This procedure migrates the data from each remote table into the base table created by the sa_migrate_create_tables procedure.

If you do not want to migrate the foreign keys from the remote database, you can skip to step 10.

Run the sa_migrate_create_remote_fks_list system procedure. For example:

CALL sa_migrate_create_remote_fks_list( 'rmt_server1' );

This procedure populates the table dbo.migrate_remote_fks_list with the list of foreign keys associated with each of the remote tables listed in dbo.migrate_remote_table_list.

You can remove any foreign key mappings you do not want to recreate on the local base tables.

Run the sa_migrate_create_fks system procedure. For example:

CALL sa_migrate_create_fks( 'local_user1' );

This procedure creates the foreign key mappings defined in dbo.migrate_remote_fks_list on the base tables.

If you want to drop the proxy tables that were created for migration purposes, run the sa_migrate_drop_proxy_tables system procedure. For example:

CALL sa_migrate_drop_proxy_tables( 'local_user1' );

This procedure drops all proxy tables created for migration purposes and completes the migration process.