CODEWITHSUNDEEP
sqlsql-server
Manju
Manju

Reputation: 2640

SQL combine 2 queries to one where 2 queries are from different database

I'm trying to combine two query results to one where both the tables are present in different databases like below:

select 
    (select COUNT(DISTINCT BaseVehicleID) as BVOld
     from BaseVehicle) Old,
    (select COUNT(DISTINCT BaseVehicleID) as BVNew
     from [EnhancedStandard_VCDB_Exported_PRD_3006].BaseVehicle) New

Here [EnhancedStandard_VCDB_Exported_PRD_3006] is a different database.

So that I need to validate the count of records in both the database.

I'm able to combine the records among queries from same database.

Can someone please tell how to combine the result from 2 queries from 2 database.

Upvotes: 0

Views: 929

Answers (4)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36591

You can use UNION ALL to combine the result of both the queries together in one result set.

Considering you're referring the database hosted on same SQL Server instance, If not you need to refer the the table on remote server using a Linked Server, like LinkedServerName.DatabasName.SchemaName.TableName.

If you've both the databases on same server you can use following query, alert, I'm considering your table is under default schema i.e. dbo.

Select  COUNT(DISTINCT BaseVehicleID) as BVOldCount
from BaseVehicle
UNION ALL
Select  COUNT(DISTINCT BaseVehicleID) as BVNewCount
from [EnhancedStandard_VCDB_Exported_PRD_3006].dbo.BaseVehicle;

Or 

 Select  COUNT(DISTINCT BaseVehicleID) as BVOldCount, 'BVOldCount' as Type
 from BaseVehicle
 UNION ALL
 Select  COUNT(DISTINCT BaseVehicleID) as BVNewCount, 'BVNewCount' as Type
 from [EnhancedStandard_VCDB_Exported_PRD_3006].dbo.BaseVehicle;

Upvotes: 1

Gagan Sharma
Gagan Sharma

Reputation: 220

If your other database is in other server, you need to create linked server and follow below query:

SELECT (SELECT count(*) FROM [serverName].[DatabaseName].dbo.TableName) +

(SELECT count(*) FROM [serverName].[DatabaseName].dbo.TableName)

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 132

Try This:

SELECT  COUNT(DISTINCT Base.BaseVehicleID) AS BVNew ,
        Old.BVOld
FROM    [EnhancedStandard_VCDB_Exported_PRD_3006].BaseVehicle AS Base
        CROSS APPLY ( SELECT    COUNT(DISTINCT B2.BaseVehicleID) AS BVOld
                      FROM      BaseVehicle AS B2
                    ) Old
GROUP BY Old.BVOld

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Are you looking for 3-part naming? If so, this will probably work:

select (select COUNT(DISTINCT BaseVehicleID)
        from BaseVehicle
       ) as Old,
       (Select COUNT(DISTINCT BaseVehicleID)
        from [EnhancedStandard_VCDB_Exported_PRD_3006].dbo.BaseVehicle
       ) New

Upvotes: 3

Related Questions