SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 How to select different tables in different db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ariffcal
Starting Member

4 Posts

Posted - 12/13/2011 :  02:42:15  Show Profile  Reply with Quote
How to select different tables in different database in SQL server and link them in a common field and create a report.

Example .

I am selecting table 'unit" in "A" database , right click, going to query design , after this i can not select databse "D" in SQL SErver and select another table and create a link to a common field .

I am new to SQL SERVER , Pls help .

Regards

Arif

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/13/2011 :  04:12:48  Show Profile  Reply with Quote
for accessing tables from different database just set the context to one db and use dbname.schema.tablename for other tables

ie for your example it will be like

USE [A]
GO
SELECT
FROM unit u
INNER JOIN D.dbo.othertable o
ON ....


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ariffcal
Starting Member

4 Posts

Posted - 12/17/2011 :  23:54:28  Show Profile  Reply with Quote
I am using Sql server GUI , like + StoreDb , the + expands and gives table names , I select table A , Then I want to go to another database like + inventory , expand + select table B , join in material code ( column ) , like this
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/18/2011 :  00:40:24  Show Profile  Reply with Quote
quote:
Originally posted by ariffcal

I am using Sql server GUI , like + StoreDb , the + expands and gives table names , I select table A , Then I want to go to another database like + inventory , expand + select table B , join in material code ( column ) , like this


in that case also select one of the db (in my example [A]). click new query button from top to launch a query window. paste the query and execute and you will get the results

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ariffcal
Starting Member

4 Posts

Posted - 12/18/2011 :  02:34:17  Show Profile  Reply with Quote
After making the query for the first table from (database A), I can not select the other database B , if I right click on the query it only shows the tables with database A , it does not let me go to database B , how to select database B ( second database in GUI ) , so that I can access the tables in database B . After this I want to make a join in one column between two tables and make a query .
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/18/2011 :  04:06:37  Show Profile  Reply with Quote
quote:
Originally posted by ariffcal

After making the query for the first table from (database A), I can not select the other database B , if I right click on the query it only shows the tables with database A , it does not let me go to database B , how to select database B ( second database in GUI ) , so that I can access the tables in database B . After this I want to make a join in one column between two tables and make a query .


No need to select second db. just paste query in window and execute. It already has reference to second database in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ariffcal
Starting Member

4 Posts

Posted - 12/18/2011 :  07:37:58  Show Profile  Reply with Quote
This is giving only result from first table .

After making the query for the first table from (database A),


I can not select the other database B , if I right click on the query it only shows the tables with database A , it does not let me go to database B , how to select database B ( second database in GUI ) , so that I can access the tables in database B . After this I want to make a join in one column between two tables and make a query .
Can not do the above
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/18/2011 :  10:13:13  Show Profile  Reply with Quote
quote:
Originally posted by ariffcal

This is giving only result from first table .

After making the query for the first table from (database A),


I can not select the other database B , if I right click on the query it only shows the tables with database A , it does not let me go to database B , how to select database B ( second database in GUI ) , so that I can access the tables in database B . After this I want to make a join in one column between two tables and make a query .
Can not do the above


no need of selecting database B. just prefix in query you wrote B.dbo. for all tables that are from database B and it will work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000