How to select different tables in 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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

How to select different tables in different db

Author

Topic

ariffcal
Starting Member

4 Posts

Posted - 2011-12-13 : 02:42:15

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

52326 Posts

Posted - 2011-12-13 : 04:12:48

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/

ariffcal
Starting Member

4 Posts

Posted - 2011-12-17 : 23:54:28

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 00:40:24

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/

ariffcal
Starting Member

4 Posts

Posted - 2011-12-18 : 02:34:17

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 .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 04:06:37

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/

ariffcal
Starting Member

4 Posts

Posted - 2011-12-18 : 07:37:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 10:13:13

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources