Import data from databases.

[Former Member]

Hi.

I have two databases, I'd like to make a INSERT INTO database1.table ( SELECT * FROM database2.table );

Is it possible? I'd like this rather than export manually and import later. I'd like to keep the script SQL.

[Former Member]

Like I told you today at office, it's possible! But is too much work for just few tables and 1 import operation. In this case, I think the better option is unload/reload.

[VolkerBarth · Accepted solution]

You can use proxy tables for that - have a look at the Remote Data Access feature.

Basically, you have to do the following in database 1:

1. Create a server that points to the other database - if you have a ODBC DSN "db2", then you might specify something like

   CREATE SERVER Server2 CLASS 'SAODBC' USING 'db2';

2. Create an externlogin to that server (unless the user credentials are the same on both databases, then this should not be necessary), something like

   CREATE EXTERNLOGIN User1 TO Server2 REMOTE LOGIN MyUser2 IDENTIFIED BY MyPwd2;

3. Create a proxy table for the table of interest:

   CREATE EXISTING TABLE table2 at 'Server2...MyTable';

Then you can access that remote table under the name table2, such as

INSERT INTO table1
SELECT * FROM table2
ORDER BY 1;

Note, however, that the performance to access remote data might be much slower compared to local access. This is particularly true if you are joining over local and remote data. Nevertheless, this is a very useful feature.

[Breck_Carter · Accepted solution]

Performance is MUCH improved in Version 12.

[Former Member · Accepted solution]

Thanks for the guidelide!

[Former Member · Accepted solution]

I need help with this and I am misunderstanding some basic concepts. I will try to keep my ignorance simple. I test my sql statements in isql sybase 10. I can never seem to get items to work. First am I thinking wrong? can I do the above in isql for testing before I write my vb6 code? or is it tested in another interface?

[VolkerBarth · Accepted solution]

Yes, you can run this code in Interactive SQL (and for testing or "learning" I would strongly recommend to use ISQL - or Sybase Central - over using any data access API).

Are you gettting errors from your statements?