Forum Discussion

Believer's avatar
Believer
Advocate IV
3 years ago
Solved

How do I combine custom SQL across databases into a single table?

I have the following code which efficiently concatenates the same table from several servers/databases.  (Thanks again, Anonymous !!)  I added a declaration for some custom SQL.  How do I adapt the "Table.SelectRows..."  to use the custom SQL in place of the table?

 

let
TableName = "tblMyData",
CustomSQL = "Select top 10 Col1, Col2, Col4 From tblMyData Where Col5>0",

Source = Table.Combine({
Table.SelectRows(Sql.Database("SQLServer1", "MyDatabase1"), each [Item] = TableName){[Schema="dbo",Item=TableName]}[Data],
Table.SelectRows(Sql.Database("SQLServer2", "MyDatabase2"), each [Item] = TableName){[Schema="dbo",Item=TableName]}[Data],
Table.SelectRows(Sql.Database("SQLServer3", "MyDatabase3"), each [Item] = TableName){[Schema="dbo",Item=TableName]}[Data],
Table.SelectRows(Sql.Database("SQLServer4", "MyDatabase4"), each [Item] = TableName){[Schema="dbo",Item=TableName]}[Data],
Table.SelectRows(Sql.Database("SQLServer5", "MyDatabase5"), each [Item] = TableName){[Schema="dbo",Item=TableName]}[Data]})
in
Source

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Believer 

     

    Based on my test, you can try the following code. Sql.Database - PowerQuery M

     

    let
    CustomSQL = "SELECT * FROM Table_1 WHERE Provider='a';",
    Table1 = Sql.Database("SQLServer1", "Database1", [Query=CustomSQL]),
    Table2 = Sql.Database("SQLServer2", "Database2", [Query=CustomSQL]),
    Source = Table.Combine({Table1, Table2})
in
    Source

     

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Believer 

     

    Based on my test, you can try the following code. Sql.Database - PowerQuery M

     

    let
    CustomSQL = "SELECT * FROM Table_1 WHERE Provider='a';",
    Table1 = Sql.Database("SQLServer1", "Database1", [Query=CustomSQL]),
    Table2 = Sql.Database("SQLServer2", "Database2", [Query=CustomSQL]),
    Source = Table.Combine({Table1, Table2})
in
    Source

     

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.