Re: Link btw 2 tables from different DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Re: Link btw 2 tables from different DB

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-05-27 : 12:04:30

Hi,

I have two databases namely GPDb and XPDb.

I want to link a table called tblImport in XPDb to table called TblImport2 in GPDb. Both tables have a common field called GPID.

Therefore is it possible to then write a query/view in XPDb to be able to read certain fields from GPDb when XPDb.TblImport2.GPID = GPDb.TblImport.GPID

Any help please

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 12:11:54

select t2.<cols>,t1.<cols>
from [GPDB].[owner].TblImport2 t2
inner join
[XPDB].[owner].tblImport t1
on
t2.GPID = t1.GPID

Everyday I learn something that somebody else already knew

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-05-27 : 12:12:57

Yes, use 3 part joins or even 4 if they are on different servers:

SELECT <yourfields> FROM <localtable> AS LT
JOIN <linkedserver>.<database>.<schema>.<tablename> As RT
ON RT.<joinfield> = LT.<joinfield>

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-05-27 : 12:23:04

select t2.<cols>,t1.<cols>

I am confused with the above statement, lets say you want to select fields gender, weight and height from TblImport2 in GPDb and also select field age from TblImport in XPDb.. Will it be

select t2.<gender, weight, height>, t1.<age>

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 12:42:12

sorry for the confusion, Use t2.gender,t2.weight,t2.height,t1.age

Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-05-28 : 05:03:21

Ok, I have a sample of the databases and fields as shown in the code below.

select t2.GPRDPracID, t2.address1,t1.GPRDPracID
from [GPRDTech].[gprdsql].TblPracDetails t2
inner join
[Verisis].[dbo].tblImport t1
on
t2.GPRDPracID = t1.GPRDPracID

I am running it in Verisis database and receive the error,

Msg 207, Level 16, State 1, Line 6
Invalid column name 'GPRDPracID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'GPRDPracID'.

However, GPRDPracID is a common field in both tables.

Any help please

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 05:19:43

is gprdsql a schema?

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-05-28 : 06:16:18

yes GPRDTech has gprdsql as the schema and Verisis dbo..

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 07:55:46