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.

asked 13 May '10, 12:28

Ismael's gravatar image

Ismael
2026612
accept rate: 50%

edited 13 May '10, 19:03

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819

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.

(14 May '10, 02:00) Zote

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.

permanent link

answered 13 May '10, 19:03

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 06 Jun '10, 08:44

3

Performance is MUCH improved in Version 12.

(13 May '10, 19:18) Breck Carter

Thanks for the guidelide!

(14 May '10, 11:44) Ismael

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?

(16 May '12, 12:43) George
Replies hidden

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?

(16 May '12, 12:56) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×69
×56
×18
×4

question asked: 13 May '10, 12:28

question was seen: 2,697 times

last updated: 16 May '12, 12:56