Use a list of databases from one query into another query cursor

Hi All,

I've got hundreds of databases with identical tables structure and tens of queries to run against these dbs. To avoid loading list of db for each query cursor, I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries. The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it.

DECLARE
    @db VARCHAR(128),
	@query VARCHAR(MAX), 
    @sql VARCHAR(MAX)

SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''

CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128))

DECLARE db_cursor CURSOR FOR

--I want to replace this line with a already loaded recordset 'DBs'
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #Tables

DROP TABLE #Tables

Regards,

Przemek

Hi przemek_pbi

For this requirement,

"I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries."

you could consider to disable"parallel loading of tables" so that power bi would not load mutiple queries in parallel.

"The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it."

From above I'm not clear which situation you meet,

did you import data from database and need filter the columns or rows which needs to be load in,

or, have you alreadly load data in, then you need to create measures or columns,a new table based on your dataset in power bi?

If it is the first scenario, also if your database is a sql database, you could write sql statement to define which data should be load in.

Reference here

https://support.office.com/en-us/article/Import-Data-from-Database-using-Native-Database-Query-Power-Query-F4F448AC-70D5-445B-A6BA-302DB47A1B00

if it the second one,  for the bold line, it can be transformed to DAX formula as below

SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

New table
new table = SUMMARIZE(FILTER(ALL(Table1),NOT[name] IN {"master","model","msdb","tempdb"}),[name])

Or column
Column = CALCULATE(MAX([name]),FILTER(ALL(Table1),NOT[name] IN {"master","model","msdb","tempdb"}))

Best Regards

Maggie