SQL Server multiple database join and sum

Question

I have multiple databases all containing the same structure but not the same content, A001, A002, A003, A004....A020 what is the quickest way to reformulate the query in a short & convenient way so that I can get the table output as follows:

Code | Desc | A001 | A002 | A003 | A004 
=====+======+======+======+======+=======
ABCD | ABCD |  0   |   1  |   3  |  4

The query is as follows for A001

SELECT AC1.cCode, SUM(AC1.cQtyin) - SUM(AC1.cQtyout) AS A001, AB1.cDes
FROM
A001.dbo.pmStock AC1 
INNER JOIN
A001.dbo.pmProduct AB1 ON AC1.Id = AB1.Prid
GROUP BY AC1.cCode, AB1.cDes

Its a bit confusing on how to do the grouping, and if theres a convenient way to loop around or something to I dont have to reiterate for each A001 to A020. Is this possible in SQL? Particularly using multiple databases (A001 and A002.... are all different databases on the same server)

If ABCD exists in A001, will it exist in all the other database tables too? — MatBailie
– MatBailie, Commented Dec 5, 2012 at 23:29
Yes thats correct, just the values for quantities are different and out are different, the code is only used to identify the product but its replicated many many times — Tarang
– Tarang, Commented Dec 5, 2012 at 23:37

MatBailie · Accepted Answer · 2012-12-06 00:10:59Z

I'm on my iPhone, so this is a bit of a headache to type. But, the following may be at least a little maintainable. It may be worth making the sub query into a view.

SELECT
  Product.cDes,
  Stock.cCode,
  SUM(CASE WHEN Stock.source = 'A001' THEN Stock.amount ELSE 0 END) AS A001,
  SUM(CASE WHEN Stock.source = 'A002' THEN Stock.amount ELSE 0 END) AS A002,
  SUM(CASE WHEN Stock.source = 'A003' THEN Stock.amount ELSE 0 END) AS A003,
  Etc, Etc
FROM
  A001.dbo.Product
INNER JOIN
(
  SELECT 'A001' AS source, id, cCode, cQtyIn - cQtyOut AS amount FROM A001.dbo.pmStock
  UNION ALL
  SELECT 'A002' AS source, id, cCode, cQtyIn - cQtyOut AS amount FROM A002.dbo.pmStock
  UNION ALL
  SELECT 'A003' AS source, id, cCode, cQtyIn - cQtyOut AS amount FROM A003.dbo.pmStock
  UNION ALL
  Etc, etc
)
  AS Stock
    ON Stock.Id = Product.Prid
GROUP BY
  Stock.cCode,
  Product.cDes

usr · Accepted Answer · 2012-12-05 23:26:02Z

0

You have to specify all database names, table names and columns statically (they must be constants). This means that you can either write this query manually or build a SQL string (either in T-SQL or on the client) and execute it dynamically.

answered Dec 5, 2012 at 23:26

usr

172k38 gold badges251 silver badges380 bronze badges

Collectives™ on Stack Overflow

SQL Server multiple database join and sum

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related