How can I code a single SQL select statement that references two different databases in the same SQL select statement in Perl using DBI? I.E., There are two Microsoft SQL server databases, Database1 and Database2 . I need to be able to do this in Perl:
select d.MyData, t.OtherData
from database1.dbo.TableInDatabase1 d
left outer join database2.dbo.TableInDatabase2 t on t.CommonIDKey = d.CommonIDKey
Any help on this is greatly appreciated.
Normally you connect to one database at time:
$DB = DBI->connect("DBI:ODBC:$DSN", $user, $pw) or die "Couldn't open database: '$DBI::errstr'; stopped";
$sth = $DB->prepare ($UseQuery) or die "couldn't do it";
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";
but I need a solution that lets a SINGLE SQL statement in Perl access BOTH Database1 and Database2 in the same SQL statement.
Doing separate connects to each database and pulling data from each database independently is NOT an option.