Question Re: Import data from databases. in Technology Q&A

Import data from databases.

Former Member — Thu, 13 May 2010 12:28:23 GMT

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.

Re: Import data from databases.

VolkerBarth — Thu, 13 May 2010 19:03:04 GMT

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:

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';
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;
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.

Re: Import data from databases.

Breck_Carter — Thu, 13 May 2010 19:18:00 GMT

Performance is MUCH improved in Version 12.

Re: Import data from databases.

Former Member — Fri, 14 May 2010 02:00:02 GMT

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.

Re: Import data from databases.

Former Member — Fri, 14 May 2010 11:44:25 GMT

Thanks for the guidelide!

Re: Import data from databases.

Former Member — Wed, 16 May 2012 12:43:34 GMT

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?

Re: Import data from databases.

VolkerBarth — Wed, 16 May 2012 12:56:01 GMT

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?