Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » System Procedures » System procedures

sa_materialized_view_info system procedure Next Page

sa_migrate system procedure

Migrates a set of remote tables to a SQL Anywhere database.


sa_migrate( base_table_owner,
[, table_name ]
[, owner_name ]
[, database_name ]
[, migrate_data ]
[, drop_proxy_tables ]
[, migrate_fkeys ]


You can use this procedure to migrate tables to SQL Anywhere from a remote Oracle, DB2, SQL Server, Adaptive Server Enterprise, or SQL Anywhere database. This procedure allows you to migrate in one step a set of remote tables, including their foreign key mappings, from the specified server. The sa_migrate system procedure calls the following system procedures:

You might want to use these system procedures instead of sa_migrate if you need more flexibility. For example, if you are migrating tables with foreign key relationships that are owned by different users, you cannot retain the foreign key relationships if you use sa_migrate.

Before you can migrate any tables, you must first create a remote server to connect to the remote database using the CREATE SERVER statement. You may also need to create an external login to the remote database using the CREATE EXTERNLOGIN statement. See CREATE SERVER statement and CREATE EXTERNLOGIN statement.

You can migrate all the tables from the remote database to a SQL Anywhere database by specifying only the base_table_owner and server_name parameters. However, if you specify only these two parameters, all the tables that are migrated will belong to one owner in the target SQL Anywhere database. If tables have different owners on the remote database and you want them to have different owners on the SQL Anywhere database, then you must migrate the tables for each owner separately, specifying the base_table_owner and owner_name parameters each time you call the sa_migrate procedure.


Do not specify NULL for both the table_name and owner_name parameters. Supplying NULL for both the table_name and owner_name parameters migrates all the tables in the database, including system tables. As well, tables that have the same name, but different owners in the remote database all belong to one owner in the target database. It is recommended that you migrate tables associated with one owner at a time.



Side effects


See also

The following statement migrates all the tables belonging to user p_chin from the remote database, including foreign key mappings; migrates the data in the remote tables; and drops the proxy tables when migration is complete. In this example, all the tables that are migrated belong to local_user in the target SQL Anywhere database.

CALL sa_migrate( 'local_user', 'server_a', NULL, 'p_chin', NULL, 1, 1, 1 )

The following statement migrates only the tables that belong to user remote_a from the remote database. In the target SQL Anywhere database, these tables belong to the user local_a. Proxy tables created during the migration are not dropped at completion.

CALL sa_migrate( 'local_a', 'server_a', NULL, 'remote_a', NULL, 1, 0, 1 )