SQL SERVER JOINS CONCEPT:

  • Thread starter Thread starter venumadhav g
  • Start date Start date
V

venumadhav g

Hi Folks,
I am useing SQL Server 2005. I want to join two different tables data from
two different Databases(Ex: 1. Database_X 2. Database_Y). Both Available
DBs having one common field..A

Can I have the Query for the above..?

dbname1:Database_X:
Tablename- OneTable
FieldsNames: A B C D
dbname2:Database_Y:
Tablename- TwoTable
FieldsNames: A M N O


How to take out put as " A B C M N"

:Please reply ASAP
 
What Access exposes that kind of functionality with a LINKed tables, MS SQL
Server may expose it as linked server. See sp_addlinkedserver in BOL, if you
cannot do it in a user friendly manner through the tools at hand. Once the
other database is added as a linked 'database', you can use the MS SQL
Server four parts name to access the table:


SELECT *
FROM linkedServerName.databaseName.schemaName.tableName


You can also use OPENROWSET, if you don't want to explicitly create a linked
server:

SELECT .*
FROM OPENROWSET( 'Microsoft.Jet.OLEDE.4.0',
'C:\whatever\northwind.mdb' ;
'admin';
' ',
SomeCrosstab )


(note the strange mix of ; and , between the 'arguments': :while OPENROWSET
appears as being a function, it is a statement (a macro)).

See the BOL for more examples.



Vanderghast, Access MVP
 
venumadhav said:
Hi Folks,
I am useing SQL Server 2005. I want to join two different tables data
from two different Databases(Ex: 1. Database_X 2. Database_Y). Both
Available DBs having one common field..A

Can I have the Query for the above..?

dbname1:Database_X:
Tablename- OneTable
FieldsNames: A B C D
dbname2:Database_Y:
Tablename- TwoTable
FieldsNames: A M N O


How to take out put as " A B C M N"
Click to expand...

If the two databases are on the same SQL Server instance then...

SELECT tbl1.A, tbl1.B, tbl1.C, tbl2.M, tbl2.N
FROM Database_X.dbo.OneTable AS tbl1
JOIN Database_Y.dbo.TwoTable AS tbl2
ON tbl1.A = tbl2.A

The above assumes that "dbo" is the owner of the tables. If not you would
have to replace dbo with the correct owner name.

If the two databases are on different servers then you would have to have
one set up as a linked server to the other one. Then the name of the server
link would be added in front of the table qualifier (before the database
name) followed by another "dot".

EX: ServerLinkName.Database_X.dbo.OneTable
 
Back
Top