CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
Cornelius
Cornelius

Reputation: 1027

SQL return database name in a query comparing one table across three databases

I have created a query that compares one table across three databases.

My query code is:

SELECT item, description, stocked, product_code, cost_method, cost_type
FROM
(
    SELECT item, description, stocked, product_code, cost_method, cost_type
        FROM [Arborg_Test_App].[dbo].item
    UNION ALL
    SELECT item, description, stocked, product_code, cost_method, cost_type 
        FROM [Teulon_Test_App].[dbo].item
    UNION ALL
    SELECT item, description, stocked, product_code, cost_method, cost_type
        FROM [PA_Test_App].[dbo].item
) data

GROUP BY item, description, stocked, product_code, cost_method, cost_type
HAVING COUNT(*) = 1

And it works great, it compares the table item across the three databases and only returns rows where the data in the selected columns doesn't match between the three databases.

My issue is, in the results that return I can't tell which row is from what database. My query results: enter image description here

What I need is an additional column in my query results that shows the database name for each row.

Upvotes: 0

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Because you are looking for singletons, you can just add the db name to each subquery and then use max() in the outer query:

SELECT item, description, stocked, product_code, cost_method, cost_type,
       max(db) as thedb
FROM
(
    SELECT item, description, stocked, product_code, cost_method, cost_type, 'arbord' as db
        FROM [Arborg_Test_App].[dbo].item
    UNION ALL
    SELECT item, description, stocked, product_code, cost_method, cost_type, 'teulon'
        FROM [Teulon_Test_App].[dbo].item
    UNION ALL
    SELECT item, description, stocked, product_code, cost_method, cost_type, 'pa'
        FROM [PA_Test_App].[dbo].item
) data
GROUP BY item, description, stocked, product_code, cost_method, cost_type
HAVING COUNT(*) = 1;

Upvotes: 3

Related Questions