July 12, 2023 at 4:08 pm
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?
July 12, 2023 at 8:39 pm
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!"
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply