I got around 18 db's. All these db's have the same structure. I want to query all these db's once to get my results.
Example:
ShopA ShopB ShopC
These db's got all the table article (and also the same rows). How do I get all articles in one result with a WHERE?
I thought:
select *
from shopa.dbo.article
shopb.dbo.article
shopc.dbo.article
where color = 'red'
Did someone got an idea?
Have you considered doing a UNION ALL?
So something like:
SELECT 'a' AS Shop, *
FROM shopa.dbo.article
WHERE color = 'red'
UNION ALL
SELECT 'b' AS Shop, *
FROM shopb.dbo.article
WHERE color = 'red'
UNION ALL
SELECT 'c' AS Shop, *
FROM shopc.dbo.article
WHERE color = 'red'
Or, with a CTE (if you RDBMS supports it)
;WITH allstores AS (
SELECT 'a' AS Shop, *
FROM shopa.dbo.article
UNION ALL
SELECT 'b' AS Shop, *
FROM shopb.dbo.article
UNION ALL
SELECT 'c' AS Shop, *
FROM shopc.dbo.article
)
SELECT *
FROM allstores
WHERE color = 'red'
you could use UNION if you can simply select the db names you could also use a cursor select with OPENQUERY on a dynamically created string insert into a temp table and select from that
You can create a View wich is populated from your select as this:
CREATE VIEW view_name AS
SELECT * FROM shopa.dbo.article
UNION
SELECT * FROM shopb.dbo.article
UNION
SELECT * FROM shopc.dbo.article
Then you can try to run a query by the View
Select * from view_name
where color = 'red'
Then if you want write another query with another condition, you don't write another big query with union or other code. You can just write a query on a VIEW
