SQL SERVER JOINS CONCEPT:

[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..?

dbname1atabase_X:
Tablename- OneTable
FieldsNames: A B C D
dbname2atabase_Y:
Tablename- TwoTable
FieldsNames: A M N O


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

lease reply ASAP

 

[Michel Walsh]

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

 

[Rick Brandt]

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..?

dbname1atabase_X:
Tablename- OneTable
FieldsNames: A B C D
dbname2atabase_Y:
Tablename- TwoTable
FieldsNames: A M N O


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

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.dbneTable 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.dbneTable

 

[venumadhav g]

Thansk Rick Brandt,
I have got the query with ur Help... thanks a lot