Skip to Content

Export parts of SQL 16 DB to another SQL 16 DB

Hello, all

I need to move data from 30+ tables from one DB to another.

The DBs have the same structure.

I need to move only some of the data according to a specific where clause.

I tried using ISQL OUTPUT statement, but, it appears I cannot pass any dynamic parameters to it.

For example, this is how I export data for 1 table

//-----------------------------------------

select * from t001_table where c_key = 'key';

output using 'driver=SQL Anywhere 16;UID=dba;PWD=pwd;Server=server_name';DBN=db_name';ASTART=No;LINKS=TCPIP();CON='''''; into "DBA"."t001_hotel_sync_timestamp" create table off;

//--------------------------------------

No, I have 30 tables like this, so I thought to make the connection string a parameter and pass it to the output statement. But I seem to fail to do that. It does not appear that OUTPUT statement is ready to accept any variables in it's parameters, only constants.

Is there any workaround to this issue?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Dec 21, 2016 at 09:34 AM

    I would recommend to use the builtin "Remote Data Access" facility, i.e. to use proxy tables to copy the data.

    Within the 2nd database, you would create a read-only remote server accessing the 1st database, then create proxy tables via CREATE EXISTING TABLE for each remote table you want to copy, and then use "SELECT * INTO MyLocalTable FROM MyRemoteTable ORDER BY Whatever" to copy the data (and to compare it afterwards).

    After that, you can remove the remote tables and the remote server.

    A sample can be found in the answer of that FAQ from the SQL Anywhere Forum:

    create existing table in local db

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 21, 2016 at 10:00 AM

    Thank you, Volker

    Remote tables tend to lock the data for editing in the target DB. There are tables where I need to move 50,000+ rows and the target DB is operational at the time. Such operation may lockout other users.

    I would rather not use it if there is an alternative.

    Add comment
    10|10000 characters needed characters exceeded

    • Remote tables tend to lock the data for editing in the target DB.

      What do you mean exactly? Are rows in the source or in the target database "locked"? - You could also use INSERT ... SELECT FROM" instead of "SELECT INTO", and the locking should generally depend on the isolation level chosen. And of course you can switch roles, i.e. create the remote server within the "source" database to access the "target" database and copy via "INSERT MyRemoteTable SELECT FROM MyLocalTable..."...

      Besides that, instead of the DBISQL OUTPUT command you can easily use the UNLOAD statement which is a real SQL statement and therefore does support variables and the like.

  • Dec 21, 2016 at 10:47 AM

    Does UNLOAD statement allow exporting directly into a different DB? I found no evidence of that in the help.

    Regarding remote tables - the reason we are not using them to retrieve data is exactly because while transaction is opened, the whole pages of the remote table a locked in the target DB. It has been a while since we used them for anything other than inserting small prices of data with immediate commit.

    Add comment
    10|10000 characters needed characters exceeded