Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

insert data from another database

5 posts in General Discussion Last posting was on 2003-10-20 11:33:53.0Z
gregory durniak Posted on 2003-10-18 23:23:04.0Z
Sender: 3f5b.3f91cbf4.1804289383@sybase.com
From: gregory durniak
Newsgroups: ianywhere.public.general
Subject: insert data from another database
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f91cc47.3f5c.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 18 Oct 2003 16:27:03 -0700, 10.22.241.42
Lines: 33
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Oct 2003 16:16:29 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Oct 2003 16:23:04 -0700
X-Trace: forums-1-dub 1066519384 10.22.108.75 (18 Oct 2003 16:23:04 -0700)
X-Original-Trace: 18 Oct 2003 16:23:04 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1877
Article PK: 4127

Adaptive Server Anywhere Version 7.0.4.2788

I am trying to insert data into a table, from a table in
another database

INSERT INTO table_1
SELECT * from table_2

where table _1 and table_2 are on the same localhost, but in
two separate databases db1, db2 (two separate ports)

is this possible?

I have tried treating db2 as an external server:

CREATE SERVER db2
CLASS 'asajdbc'
USING 'localhost:7380';

create existing table table_2
using 'db2..dba.table_2';

but I get errors, saying the server already exists, or is
not found

however, if I try:

INSERT INTO table_1
SELECT * from db2..dba.table_2

it runs, but no data is inserted

thanks


Breck Carter [TeamSybase] Posted on 2003-10-19 12:02:55.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: insert data from another database
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <7iu4pvc63nr32ga1atcq6754d2cvaf3git@4ax.com>
References: <3f91cc47.3f5c.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 19 Oct 2003 05:02:55 -0700
X-Trace: forums-1-dub 1066564975 64.7.134.118 (19 Oct 2003 05:02:55 -0700)
X-Original-Trace: 19 Oct 2003 05:02:55 -0700, bcarter.sentex.ca
Lines: 99
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1878
Article PK: 4128

If you want to re-create the remote server definition with CREATE
SERVER, you have to run a DROP SERVER first. If you have successfully
created any proxy tables, you'll have to drop them first, and the same
thing goes for external logins... there's a whole irritating hierarchy
of foreign key relationships in the system catalog tables :)

ALSO, due to a limitation, after executing the DROP SERVER you should
disconnect and re-connect to the primary database before executing the
CREATE SERVER again. That forces the middleware software to drop the
hidden connection to the old server definition; otherwise, it might
hold open the old connection using the old server definition and your
life will be *very* confusing :)

Here is an example using ASAODBC, from Section 1.38 CREATE EXISTING
TABLE in Chapter 1, Creating, in SQL Anywhere Studio 9 Developer's
Guide ISBN 1-55622-506-7 Wordware Publishing, Plano, Texas due in
2004:

-----
Here is a full, end-to-end example using two SQL Anywhere 9 databases;
the first database is the remote database and the second database
contains the proxy tables. On the first database a global permanent
table t1 is created:

CREATE TABLE t1 (
pkey INTEGER NOT NULL,
c1 VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( pkey ) );

INSERT INTO t1 VALUES ( 1, 'Hello, World' );
COMMIT;

On the second database the following commands create the server,
external login and two proxy tables. The first proxy table t1proxy
points to the existing table t1, and the second proxy table t2proxy
causes a new table t2 to be created on the remote database:

CREATE SERVER other CLASS 'ASAODBC' USING 'otherdsn';

CREATE EXTERNLOGIN DBA TO other
REMOTE LOGIN DBA IDENTIFIED BY "SQL";

CREATE EXISTING TABLE t1proxy AT 'other;otherdb;dba;t1';

SELECT * FROM t1proxy; -- displays 'Hello, World'

CREATE TABLE t2proxy (
pkey INTEGER NOT NULL,
c1 VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( pkey ) )
AT 'other;otherdb;dba;t2';

INSERT INTO t2proxy VALUES ( 1, 'Goodbye' );

SELECT * FROM t2proxy; -- displays 'Goodbye'
-----

Breck

On 18 Oct 2003 16:23:04 -0700, gregory durniak wrote:

>Adaptive Server Anywhere Version 7.0.4.2788
>
>I am trying to insert data into a table, from a table in
>another database
>
> INSERT INTO table_1
> SELECT * from table_2
>
>where table _1 and table_2 are on the same localhost, but in
>two separate databases db1, db2 (two separate ports)
>
>is this possible?
>
>I have tried treating db2 as an external server:
>
>CREATE SERVER db2
>CLASS 'asajdbc'
>USING 'localhost:7380';
>
>create existing table table_2
>using 'db2..dba.table_2';
>
>but I get errors, saying the server already exists, or is
>not found
>
>however, if I try:
>
> INSERT INTO table_1
> SELECT * from db2..dba.table_2
>
>it runs, but no data is inserted
>
>thanks

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Breck Carter [TeamSybase] Posted on 2003-10-19 12:04:04.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: insert data from another database
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <kvu4pvg3a9b1c4ujtdir4h5iouu2er6eao@4ax.com>
References: <3f91cc47.3f5c.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 19 Oct 2003 05:04:04 -0700
X-Trace: forums-1-dub 1066565044 64.7.134.118 (19 Oct 2003 05:04:04 -0700)
X-Original-Trace: 19 Oct 2003 05:04:04 -0700, bcarter.sentex.ca
Lines: 45
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1879
Article PK: 4130

Also, please note that the "databasename.ownername.tablename" syntax
is not available in ASA. So db..tbl will not work.

Breck

On 18 Oct 2003 16:23:04 -0700, gregory durniak wrote:

>Adaptive Server Anywhere Version 7.0.4.2788
>
>I am trying to insert data into a table, from a table in
>another database
>
> INSERT INTO table_1
> SELECT * from table_2
>
>where table _1 and table_2 are on the same localhost, but in
>two separate databases db1, db2 (two separate ports)
>
>is this possible?
>
>I have tried treating db2 as an external server:
>
>CREATE SERVER db2
>CLASS 'asajdbc'
>USING 'localhost:7380';
>
>create existing table table_2
>using 'db2..dba.table_2';
>
>but I get errors, saying the server already exists, or is
>not found
>
>however, if I try:
>
> INSERT INTO table_1
> SELECT * from db2..dba.table_2
>
>it runs, but no data is inserted
>
>thanks

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


gregory durniak Posted on 2003-10-20 10:35:39.0Z
Sender: 6071.3f93bb48.1804289383@sybase.com
From: gregory durniak
Newsgroups: ianywhere.public.general
Subject: Re: insert data from another database
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f93bb6e.6074.846930886@sybase.com>
References: <3f91cc47.3f5c.846930886@sybase.com><kvu4pvg3a9b1c4ujtdir4h5iouu2er6eao@4ax.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 20 Oct 2003 03:39:42 -0700, 10.22.241.42
Lines: 15
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 20 Oct 2003 03:28:55 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 20 Oct 2003 03:35:39 -0700
X-Trace: forums-1-dub 1066646139 10.22.108.75 (20 Oct 2003 03:35:39 -0700)
X-Original-Trace: 20 Oct 2003 03:35:39 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1881
Article PK: 4131


> Also, please note that the
> "databasename.ownername.tablename" syntax is not available
> in ASA. So db..tbl will not work

Thanks for your help. This is a lot harder than I had hoped.

I got the syntax from the ASA 7 User's Guide, for creating a
proxy table:

"To create a proxy table named p_employee on the current
server to a remote table named employee on the server named
asademo1, use the following syntax:"

CREATE EXISTING TABLE p_employee
AT 'asademo1..dba.employee'


Breck Carter [TeamSybase] Posted on 2003-10-20 11:33:53.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: insert data from another database
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <thh7pvg683m3s6rqik7ioi1hgmnv84j240@4ax.com>
References: <3f91cc47.3f5c.846930886@sybase.com><kvu4pvg3a9b1c4ujtdir4h5iouu2er6eao@4ax.com> <3f93bb6e.6074.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 20 Oct 2003 04:37:57 -0700, bcarter.sentex.ca
Lines: 25
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 20 Oct 2003 04:27:09 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 20 Oct 2003 04:33:53 -0700
X-Trace: forums-1-dub 1066649633 10.22.108.75 (20 Oct 2003 04:33:53 -0700)
X-Original-Trace: 20 Oct 2003 04:33:53 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1882
Article PK: 4132

Yes, that's correct, but it assumes you have run CREATE SERVER first,
otherwise "asademo1" is meaningless.

On 20 Oct 2003 03:35:39 -0700, gregory durniak wrote:

>> Also, please note that the
>> "databasename.ownername.tablename" syntax is not available
>> in ASA. So db..tbl will not work
>
>Thanks for your help. This is a lot harder than I had hoped.
>
>I got the syntax from the ASA 7 User's Guide, for creating a
>proxy table:
>
>"To create a proxy table named p_employee on the current
>server to a remote table named employee on the server named
>asademo1, use the following syntax:"
>
>CREATE EXISTING TABLE p_employee
>AT 'asademo1..dba.employee'

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com