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.

How to query from another database

4 posts in General Discussion Last posting was on 2003-07-25 14:33:49.0Z
Richard Posted on 2003-07-24 16:43:15.0Z
From: "Richard" <Richardr@ldsinc.com>
Subject: How to query from another database
Date: Thu, 24 Jul 2003 11:43:15 -0500
Lines: 9
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#QzdmPgUDHA.330@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: lds253.ldsinc.com 66.6.104.253
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1455
Article PK: 3688

I have 2 databases where I need to read tables from DatabaseA to insert data
into tables in DatabaseB. How do I write a script that can access both
database?


Thanks
Richard


Nick Elson Posted on 2003-07-24 17:23:01.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
References: <#QzdmPgUDHA.330@forums-2-dub>
Subject: Re: How to query from another database
Date: Thu, 24 Jul 2003 13:23:01 -0400
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uBW90lgUDHA.298@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: nicelson-xp.sybase.com 172.31.143.50
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1456
Article PK: 3689

While you can open multiple connections from the same dbisql session
and switch between them via their connection names (set the AS clause
of the CONNECT SQL statement and the SET CONNECTION SQL
statement) and using input/load/output/unload statements . . .

Today use of remote servers and proxy tables can be very useful. That
way you can use a simple

insert into "remote_table"
select * from "local_table"
where <your_choice_of_predicates>

to move data around conveniently or use any amount of 'normal' SQL DML
to achieve your desired results. This is especially true if you need to do
this on a regular basis or for a few tables. It is also verify useful when
mixing different RDBMS products.

"Richard" <Richardr@ldsinc.com> wrote in message
news:%23QzdmPgUDHA.330@forums-2-dub...
> I have 2 databases where I need to read tables from DatabaseA to insert
data
> into tables in DatabaseB. How do I write a script that can access both
> database?
>
>
> Thanks
> Richard
>
>


Richard Posted on 2003-07-25 12:46:40.0Z
From: Richard
Date: Fri, 25 Jul 2003 08:46:40 -0400
Newsgroups: ianywhere.public.general
Subject: Re: How to query from another database
Message-ID: <8AFC9B677E7BA647004630F285256D6E.0065F10385256D6D@webforums>
References: <#QzdmPgUDHA.330@forums-2-dub> <uBW90lgUDHA.298@forums-2-dub>
Lines: 6
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:1463
Article PK: 3696

This is great information for when I need to do this task repetitively...
and I thank you! Except I need a concrete example to help me with a one
time script for a customer needs for a conversion.

Thanks
Richard


Nick Elson Posted on 2003-07-25 14:33:49.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
References: <#QzdmPgUDHA.330@forums-2-dub> <uBW90lgUDHA.298@forums-2-dub> <8AFC9B677E7BA647004630F285256D6E.0065F10385256D6D@webforums>
Subject: Re: How to query from another database
Date: Fri, 25 Jul 2003 10:33:49 -0400
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
x-mimeole: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uEm$9rrUDHA.346@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: vpn-dub-074.sybase.com 10.22.120.74
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1464
Article PK: 3697

Actually, for a 'one time' requirement, just take
advantage of the output from dbunload.exe.

Yes, dbunload unloads the data and the schema, but
you then have a **free** section of reload.sql file that
can be simply be cut out and used as is with dbisql to
load that data into the alternate database.

This works 'just fine' if you are going to move all of the data
across.

Otherwise we are going to need a lot more details to know
what is really required as well as what is/is not possible here.

In broad strokes the script will be as I said before:

CONNECT TO <1st_srvr_name> DATABASE <1st_database_name>
AS Connection1
USER dba IDENTIFIED BY sql;

CONNECT TO <2nd_srvr_name> DATABASE <2nd_database_name>
AS Connection2
USER dba IDENTIFIED BY sql;

SET CONNECTION Connection1;
. . .
select [* | column_list] from table1 [where-clause_as_required];
output to 'c:\\temp\\table1.dat';

. . . . any thing else you need to do on Connection1 you can do here

SET CONNECTION Connection2;

input into table1 from 'c:\\temp\\table1.dat' ;

After which you can switch back and forth all you want and use all the
features
of the ISQL INPUT and OUTPUT statements. If the 2 databases are running
on the same server, or from two servers on the same machine, then you could
use LOAD TABLE and UNLOAD TABLE if they suite your other <unspecified>
requirements [like a where clause on the above select statement for
example].

Of course, for a 1-time function, scripting can be a lot more work than is
justified.

<Richard> wrote in message
news:8AFC9B677E7BA647004630F285256D6E.0065F10385256D6D@webforums...
> This is great information for when I need to do this task repetitively...
> and I thank you! Except I need a concrete example to help me with a one
> time script for a customer needs for a conversion.
>
> Thanks
> Richard