Re: connecting to two databases in SQLServer

[Michael Lee <homestoremike@YAHOO.COM>]

Hi Gary,
Thanks again for your help.  It's nice to be able to
network with other SQLServer 2000 users.

Can you do a INSERT/SELECT statement where the INSERT
is one database and the SELECT is another database?

example:

begin-SQL
INSERT INTO PS_JOB_STAGING
SELECT ...
FROM PS_JOB
WHERE ...

end-SQL

If this can be done, that would be awesome!  It would
eliminate the need to have to type in all the column
names provided that the tables are the same.

Thanks in advance,

Mike



--- Gary Kearns <Gary.Kearns@ELCOR.COM> wrote:
> yes it can be done.  we're also on SQLServer 2000.
>
> we set the connect string as follows:
> LET $PSCONNECTSTRING =
> 'DSN=EPPROD;UID=PSADMIN;PWD=TEST'
> LET $MXCONNECTSTRING =
> 'DSN=MAXTUSC;UID=MAXIMO;PWD=TEST'
>
> Then use the following to change between databases.
> We use the connect on
> each SQL to ensure that we're on the correct
> database.
>
> BEGIN-SELECT -DB$PSCONNECTSTRING
>
> PM.BUSINESS_UNIT
> FROM PS_BUS_UNIT_TBL_PM PM
>
> END-SELECT
>
> and
>
> BEGIN-SQL -DB$MXCONNECTSTRING
>
> INSERT INTO PS_EL_MX_ITM_STG
> (...)
> VALUES (...)
>
> END-SQL
>
>
> -----Original Message-----
> From: Michael Lee [mailto:homestoremike@YAHOO.COM]
> Sent: Wednesday, May 22, 2002 2:16 PM
> To: SQR-USERS@list.iex.net
> Subject: connecting to two databases in SQLServer
>
>
> Hello,
> I need to select data from one database and insert
> into a table in a second database.  I'm on a
> SQLServer
> 2000 platform and would like to use SQR to perform
> this task.
>
> I want to eliminate the need to have one SQR output
> the flatfile and one SQR to read the flatfile to
> insert into the target database.
>
> Can this be done, and if it can, how so?
>
> Thanks in advance,
>
> Mike
>
>
> __________________________________________________
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com


__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com