one query over several databases

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

General SQL Server Forums

New to SQL Server Programming

one query over several databases

Author

Topic

taylan
Starting Member

5 Posts

Posted - 2011-01-20 : 08:56:15

hi@all,

my question is a very easy one i think
i have one query, which should be executed over several databases

status:
use a
select * from table
where id=1

use b
select * from table
where id=1

what i want is, should be something like that
use a, b // i know it doesnt work :-)
select * from table
where id=1

thanks
ciao

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-20 : 09:03:32

Select ColumnName1,...ColumnNameN
From DatabaseName.Dbo.TableName A
Inner Join DatabaseName2.Dbo.TableName2 B
on A.ColumnName=B.ColumnName

If there are matching columns in both database's table you can join them and can use them in above way to get the desired result.

taylan
Starting Member

5 Posts

Posted - 2011-01-20 : 09:19:21

hi,
thanks for your quick answer but i think that was not what i meant

i have two databases, they both have a table (with the same structure + same columns etc.)

the query will just make a select but from two databases, different results

i already have my "join"s but it has nothing to do with my problem

thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-20 : 09:29:24

well then provide us some more details ..

e.g. table name, some data of both tables, your query and the required output

Perhaps we can then help you out!

taylan
Starting Member

5 Posts

Posted - 2011-01-20 : 09:42:21

ok

imagine you have for each country over the world one database.

now i want to have with one query from all databases (countries) the people how are older than 50

for one country it would like this (with two queries)

use germany
select name, age from people
where age>50

use austria
select name, age from people
where age>50

now i want just one query which can provide both results

??

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-20 : 09:57:56

you can construct a query with unions

e.g.

Select Name,Age From Germany.Dbo.people where age>50
Union All
Select Name,Age From Austria.Dbo.people where age>50
Union All
.
.
.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-20 : 10:26:40

Run this

select 'select * from '+name+'..table where id=1' from sysdatabases

Copy the result and run it

Madhivanan

Failing to plan is Planning to fail

taylan
Starting Member

5 Posts

Posted - 2011-01-20 : 10:28:56

yes, that works, thanks a lot.. my query is still too long but anyway it works :-)

thanks especially for your quick answers.. i will recommend this site to others

take care

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-20 : 10:31:52

quote:
Originally posted by taylan

yes, that works, thanks a lot.. my query is still too long but anyway it works :-)

thanks especially for your quick answers.. i will recommend this site to others

take care

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources