Collect data from multiple similar tables and append - PowerQuery/PowerBI

• pietlinden

  SSC Guru

  Points: 63572

  More actions

  July 12, 2023 at 4:08 pm

  #4233356

  I'm trying to do something similar to using a folder source for data - basically where I specify a folder, and can loop through the contents using PowerQuery and append all the files matching my criteria to a single dataset/table. But the difference is that instead of text files in a folder, I'm looking at tables in a SQL database where they all have the same structure and a similar prefix (so they're easy to find/identify).

  I could solve this by using a TVF that creates a temporary table, finds all the tables matching my pattern, and appends the contents to a said temp table... Kinda like this (yes, it's not quite right... it's just to give you an idea of where I was going... I could use a cursor to retrieve all the table names I need, and use that in some dynamic SQL ... sort of like this:

  use Scratch;
  go
  SELECT @@SERVERNAME;
  
  CREATE SCHEMA dummy;
  GO
  
  CREATE TABLE dummy.sqlt_1_2023_01(
  PersonID INT,
  BirthDate DATE);
  
  CREATE TABLE dummy.sqlt_1_2023_02(
  PersonID INT,
  BirthDate DATE);
  
  CREATE TABLE dummy.sqlt_1_2023_03(
  PersonID INT,
  BirthDate DATE);
  GO
  
  INSERT INTO dummy.sqlt_1_2023_01 VALUES (100,'5/3/1968');
  INSERT INTO dummy.sqlt_1_2023_02 VALUES (101,'6/6/1970');
  INSERT INTO dummy.sqlt_1_2023_03 VALUES (102,'7/1/1980');
  
  /* get all the table names that match the pattern.
  loop through them to append the contents to a single 
  table. 
  
  Then grab the data and import from that with PowerQuery
  */
  SELECT name
  FROM sys.all_objects
  WHERE type_desc = 'USER_TABLE'
  AND name LIKE 'sqlt_%';
  
  -- create temporary table inside sproc
  -- loop through all tables matching naming convention with FF cursor.
  -- append contents to temporary table
  -- select * from temporary table.
  CREATE TABLE #AllSQLTs (PersonID INT, BirthDate DATE);
  GO
  
  DECLARE @tableName NVARCHAR(25);
  DECLARE @sql NVARCHAR(150);
  /*
  use REPLACE to change the tablename.
  execute the sql statement
  go to next table
  */
  
  SET @sql = REPLACE('INSERT INTO #AllSQLTs
  SELECT PersonID, BirthDate
  FROM dummy.sqlt_1_2023_01','sqlt_1_2023_01','sqlt_2_2023_01');
  PRINT @sql;

  Or is there an easier way to do this in just PowerQuery -- basically mimicking a folder source, but with tables in a database instead?

   

• pietlinden

  SSC Guru

  Points: 63572

  More actions

  July 12, 2023 at 8:39 pm

  #4233552

  Never mind. I'm a knucklehead. Bring in all the tables (seems I shouldn't have to?), filter by name, expand tables, append.  "Use the force, Luke!"

  • This reply was modified 1 year, 10 months ago by  pietlinden.

• August 2, 2023 at 6:38 am

  This was removed by the editor as SPAM

• August 2, 2023 at 6:40 am

  This was removed by the editor as SPAM

• December 6, 2023 at 12:39 pm

  This was removed by the editor as SPAM