how to retreve data from two different database using join in sql server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL-Server

how to retreve data from two different database using join in sql server 2005

Posted 23-Aug-12 21:00pm

M@anish

Updated 23-Aug-12 21:19pm

v2

Add a Solution

Comments

[no name] 24-Aug-12 5:18am

hi whether u r asking like connecting oracle db and sql db?

3 solutions

Solution 1

yes you can simply use below query to retrive the data from other databases

SQL

select 
    * 
from 
   [OtherDB].[dbo].[OtherTable]

Posted 23-Aug-12 21:04pm

ssd_coolguy

Updated 23-Aug-12 21:09pm

v2

Comments

M@anish 24-Aug-12 3:08am

can you please explain this query in a simple formate (example)
ans thank you for support

ssd_coolguy 24-Aug-12 3:14am

currently you are using database1 and you want to retrive data from table1 from database2
then..
select * from database2.dbo.table1

M@anish 24-Aug-12 3:20am

how to retreve data from two different database using join in sql server 2005

ssd_coolguy 24-Aug-12 3:28am

if we correcly understands your problem..then
see the 2nd solution for join..
else exlpain more...
you want data from two diffrent server??

M@anish 24-Aug-12 5:08am

how to select two database in new query window(dropdown) i hope u under stand my querry :D

ssd_coolguy 24-Aug-12 5:29am

we can select only one database while opening new query window..
but we can go through diffrent database using below query..
use database_name
By the way you don't need to select two database while retriving data.

Solution 2

If it's on two different DataBase(catalog) on same Server :
use fully qualified DB names when you query, SEE sample :

SQL

SELECT T1* 
FROM [DB1].[dbo].[Table1] AS T1
LEFT OUTER JOIN [DB2].[dbo].[Trx] AS T2
ON T1.ID = T2.ID

If it's on two different DataBase(catalog) on different Server :
Use linked server: http://msdn.microsoft.com/en-us/library/ms188279.aspx[^]

Posted 23-Aug-12 21:10pm

Kuthuparakkal

Comments

M@anish 24-Aug-12 3:20am

how to retreve data from two different database using join in sql server 2005

M@anish 24-Aug-12 5:08am

how to select two database in new query window(dropdown) i hope u under stand my querry :D

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2012-08-23T22:44:00

Solution 3

see this example

SQL

select a.*,b.* from
DB1.dbo.tbl1 as a --DB1= first database name, dbo=schema(fix no change), tbl1=table from first database, a= alias of whole path (DB1.dbo.tbl1)
left join
DB2.dbo.tbl2 as b  --DB2= second database name, dbo=schema(fix no change), tbl2=table from second database, b= alias of whole path (DB2.dbo.tbl2)
on a.col1=b.col1 --join based on columns of two different tables from two different database

Happy coding!
:)

Posted 23-Aug-12 22:44pm

Aarti Meswania

Comments

__TR__ 24-Aug-12 4:47am

My 5!

Aarti Meswania 24-Aug-12 4:49am

thank you! :)

M@anish 24-Aug-12 5:08am

hi aarti is this possible we can retrieve data from different database(table) in sql server 2005 how to select two database in new query window(dropdown) i hope u under stand my querry :D

Aarti Meswania 24-Aug-12 5:12am

yes, I can understand. it is possible.
see above query create same database & tables in your sql
select 'db1' in combolist of database names
put above query and execute it will work
because we are accessing full path starting from db_name
:)

M@anish 24-Aug-12 6:48am

thanx 0_-)

Aarti Meswania 24-Aug-12 6:49am

welcome :)