Dynamic Query to Union Multiple Databases

Dynamic query to Union multiple databases

If you know the exact number of DBs:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
  SELECT C1,C2,C3
  FROM db' + CONVERT(VARCHAR(2), n) + '.dbo.A'
FROM 
(
  SELECT TOP (50) n = ROW_NUMBER() 
  OVER (ORDER BY [object_id])
  FROM sys.all_columns
) AS x;

SET @sql = STUFF(@sql, 1, 11, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

If you don't know that there are exactly 50, then this is probably better (it also allows you to exclude those that are not online):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
  SELECT C1,C2,C3
  FROM ' + QUOTENAME(name) + '.dbo.A'
FROM sys.databases
WHERE state = 0
AND name LIKE N'db[0-9]%';

SET @sql = STUFF(@sql, 1, 11, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

Displaying multiple database tables in one table

If you need a single resultset and all tables have the same layout, this should work:

DECLARE @sql nvarchar(4000) =
    (SELECT STRING_AGG(CONCAT(
        'SELECT ''',
        QUOTENAME(name),
        ''',
        * FROM ',
        QUOTENAME(name),
        '..Table ',
        CHAR(10)
        ), ' UNION ALL ' + CHAR(10))
    FROM sys.databases);

SELECT @sql;  -- for checking
EXEC(@sql);

If you're on compatibility level 130 or lower, you will have to use XML PATH(TYPE, '') to aggregate. I will leave that to you.

Union of multiple Database queries with same parameters

Implementing this "merge results from n different DBs" is rather common.
Most of the times, this is done by means of a data warehouse.

HANA allows creating virtual tables that represent tables or views in remote systems - which is the basis for an integration scenario very popular with HANA sales folks: "...simply integrate all your DBs in HANA... no data warehouse and heavy data lifting required..."

I assume this is one of those scenarios.

So, what options are there to only have to specify the selection parameters once?

A simple approach would be to use query parameters.
This can be done either via user defined table functions or parameterized views (yes, also via calculation views and parameters, but I will skip this here).

So, with this one could write something like this:

CREATE VIEW CombinedOutletBalances 
                (startReferencePeriod NVARCHAR(10),
                 endReferencePeriod  NVARCHAR(10))
    as
    
    WITH selAcctCodes as 
       (SELECT '105004001' as "AcctCode" FROM DUMMY UNION ALL 
        SELECT '105005001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105006001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105007001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105008001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105009001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105104001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105105001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105106001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105107001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105108001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '105109001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '106001001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '107009001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '109018001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '109022001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '201001001' as "AcctCode" FROM DUMMY UNION ALL  
        SELECT '201002001' as "AcctCode" FROM DUMMY) 
       
    SELECT 
        'Outlet1' AS "DataSource", T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
    FROM Db1.Table1 T0  
        INNER JOIN Db1.Table2 T1 ON T0."Account" = T1."AcctCode" 
        INNER JOIN Db1.Table3 T2 ON T0."TransId" = T2."TransId"
        INNER JOIN selAcctCodes sac ON T1."AcctCode" = sac."AcctCode"
    WHERE 
             '01.01.0001' <= :startReferencePeriod
        AND :endReferencePeriod >= T0."RefDate"   
    GROUP BY 
            T1."AcctCode", T1."AcctName"
 UNION ALL  
    SELECT 
         'Outlet2' AS "DataSource", T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
        FROM Db2.Table1 T0  
            INNER JOIN Db2.Table2 T1 ON T0."Account" = T1."AcctCode" 
            INNER JOIN Db2.Table3 T2 ON T0."TransId" = T2."TransId"
            INNER JOIN selAcctCodes sac ON T1."AcctCode" = sac."AcctCode"
    WHERE 
             '01.01.0001' <= :startReferencePeriod
        AND :endReferencePeriod >= T0."RefDate"   
    GROUP BY 
            T1."AcctCode", T1."AcctName"
 UNION ALL
    SELECT 
        'Outlet3' AS "DataSource", T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
        FROM Db3.Table1 T0  
            INNER JOIN Db3.Table2 T1 ON T0."Account" = T1."AcctCode" 
            INNER JOIN Db3.Table3 T2 ON T0."TransId" = T2."TransId"
            INNER JOIN selAcctCodes sac ON T1."AcctCode" = sac."AcctCode"
        WHERE 
                '01.01.0001' <= :startReferencePeriod
            AND :endReferencePeriod >= T0."RefDate"  
        GROUP BY 
            T1."AcctCode", T1."AcctName";

This reduces the repetition to the minimum of what can be done in pure HANA SQL.

If the selection for the AcctCode should be more flexible then the next best option would be to fill a temporary table with the selected codes and join that instead of the common table expression.

Note that I pulled the DataSource into the actual data queries, that way the result set can still be handled in further queries and reporting tools without screwing up the result data (e.g. with the "rows in-between" approach you wouldn't be able to correctly calculate the average any more).

Also note, that this may not be very well-performing, if the different source table really are on remote databases. You may want to test this extensively.

How to Get Data from Multiple Database Dynamically?

I'm always waiting for upvotes and acknowledgements ;)

With this databases (they are all the same of course, so i post only one of them.

use `company3`;
DROP TABLE  IF EXISTS Employee;
CREATE TABLE Employee
(`EmployeeID` int, `LastName` varchar(40), `Firstname` varchar(40), `Age` int)
;

INSERT INTO Employee
(`EmployeeID`, `LastName`, `Firstname`, `Age`)
VALUES
(1, 'Hansen', 'Han', 30),
(2, 'Svendson', 'Sven', 23),
(3, 'Pettersen', 'Peter', 20)
;
DROP TABLE  IF EXISTS Sales;
CREATE TABLE Sales
(`EmployeeID` int, `TransactionDate` datetime)
;

INSERT INTO Sales
(`EmployeeID`, `TransactionDate`)
VALUES
(1, '2015-12-20 10:01:00'),
(1, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00'),
(2, '2015-12-20 10:01:00')
;

And this stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetSakesConut`()
BEGIN
  DECLARE bDone INT;
  DECLARE DBname TEXT;
  DECLARE sqlstement LONGTEXT;
  DECLARE n INT;
  DECLARE curs CURSOR FOR  SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
      WHERE SCHEMA_NAME LIKE 'company%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1; 
  OPEN curs;

  SET bDone = 0;
  SET n =0;
  SET sqlstement  = '';
  SALESloop: LOOP
    FETCH curs INTO DBname; 
    IF bDone = 1 THEN
      LEAVE SALESloop;
    END IF;
    IF n>0  THEN
     SET sqlstement  = CONCAT(sqlstement,' UNION ');
    END IF;
    SET sqlstement  = CONCAT(sqlstement,'Select "',DBname,'",a.EmployeeID,');
    SET sqlstement  = CONCAT(sqlstement,'Count(b.TransactionDate) ');
    SET sqlstement  = CONCAT(sqlstement,'From ',DBname,'.Employee as a ');
    SET sqlstement  = CONCAT(sqlstement,'Inner Join ',DBname,'.Sales as b ');
    SET sqlstement  = CONCAT(sqlstement,'On a.EmployeeID=b.EmployeeID ');
    SET sqlstement  = CONCAT(sqlstement,'Group By a.EmployeeID ');
    SET n =n+1;   

  END LOOP SALESloop; 
  CLOSE curs;
  SET @sqlstement = sqlstement;
  PREPARE stmt FROM @sqlstement;
  EXECUTE stmt;
END

For the explanation:
For the cursor curs i get all Database names that start with compan
In the loop i get one Dataase name after another and i build with it
your select statement with the correct database names.
And of course ou have to add union to all Select without the first

you get folloowing Result

company1   EmployeeID   Count(b.TransactionDate)
company1   1            2
company1   2            3
company2   1            2
company2   2            3
company3   1            2
company3   2            3

Of course i had to adept the select statement because yours didn't work properly.

SQL - Select from Multiple databases

I'd create a view which combines the select statements. e.g.

CREATE VIEW v_ICITEM
AS
SELECT * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT * FROM AU1DAT.dbo.ICITEM
go;

You could include the source database as a column also:

CREATE VIEW v_ICITEM
AS
SELECT 'CN2DAT' AS Db, * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT 'AU1DAT', * FROM AU1DAT.dbo.ICITEM
go;

How to loop on multiple databases and tables

I'm also a little uncertain as to what you're hoping for, for example if you need the resulting output to be in two permanent tables or if you just need the result when queried. Of course once you build your SELECT you can return it to the caller or put it into a table, so I'll leave that up to you.

If your databases are unchanging, then of course you can just write your query and maybe put it into a VIEW for convenience:

SELECT columns from database1.dbo.RefreshLog
UNION ALL
SELECT columns from database2.dbo.RefreshLog
...

and so on

But if you're saying that your databases are themselves dynamic, in other words that databases may be created or dropped over the lifetime of your project, then you could consider using the "undocumented" procedure sp_msforeachdb to build up a list of databases, and then use THAT list to build your UNION query. Here's a quick script that captures the names of all databases that include a specific table ("Products" in the example):

IF object_id('tempdb..#DatabaseNames') IS NOT NULL
   DROP TABLE #DatabaseNames

CREATE TABLE #DatabaseNames (DatabaseName SYSNAME)

execute sp_msforeachdb @command1=
N'IF EXISTS(SELECT * FROM [?].sys.tables WHERE Name = ''Products'')
     INSERT #DatabaseNames VALUES(N''Database [?]'')'

SELECT * FROM #DatabaseNames

Combine Queries using Union All Dynamically

You have 3 options that I can think of:

1) Manually update the union in your query each year - not ideal but probably better than option 2.

2) Use dynamic SQL to build the query and it can automatically build the query based on the date the query is run. Its a bit ugly thought and performance might not be great.

3) This would be my preferred option, run a regular maintenance task to populate a completely separate table, in a single database, with just the data required for the report.

---

Option 2 might look like:

declare @StartYear int = 2015, @EndYear int = datepart(year, getdate()), @sql nvarchar(max) = '', @Index int;
set @Index = @StartYear;

declare @Years table ([Name] varchar(128));

while @Index <= @EndYear begin
  insert into @Years ([Name])
    select 'BaseTableName' + convert(varchar, @Index);
  set @Index = @Index+1;
end

select @sql = @sql + case when len(@sql) > 0 then ' union all ' else '' end + 'select [Order], [Year] from ' + [Name]
from @Years

select @sql
--exec(@sql)

---

Related Topics