Combine multiple tables into one in Snowflake

Question

Let's say I have the following monthly tables with table names formatted such that the number after the underscore refers to the month. What I want to do is to combine these 12 tables into one without having to write 10-30 insert/union all statements

table_1
table_2
table_3
table_4
table_5
table_6
table_7
table_8
table_9
table_10
table_11
table_12 -- (only 12 in this instance but could be as many as 36)

My current approach is to first create the master table with data from table_1.

create temporary table master_table_1_12 as
select *                                       -- * to keep it simple for this example
from table_1;

Then use variables such that I can simply keep hitting the run button until it errors out with "table_13 does not exist"

set month_id=(select max(month_id) from master_table_1_12) + 1;
set table_name=concat('table_',$month_id);

insert into master_table_1_12 
select * 
from identifier($table_name);

Note: All monthly tables have a month_id column

Sure it saves some space on the console(compared to multiple inserts), but I still have to run it 12 times. Are Snowflake Tasks something I could use for this? I couldn't find a fitting example from their documentation to code that up but, if anyone had success with that or with a Javascript based SP for a problem like this, please enlighten.

Answer 1

I ended up creating a UDF that spits out a create view statement and a stored procedure that executes it to create a temporary view. I work with tables following specific naming convention, so you might have to tweak this solution a little for your use case. The separation of UDF and stored proc actually helps with that as you'd mostly need to tweak the SQL UDF. I am sharing a simplified version of what I actually have in the interest of keeping it representative of the tables I listed in my question.

SQL UDF FOR GENERATING A CREATE VIEW STATETEMENT

create or replace function sandbox.public.define_view(table_pattern varchar, start_month varchar, end_month varchar)
returns table ("" varchar) as 

$$
with cte1(month_id) as

(select start_month::int + row_number() over (order by 1) - 1 
 from table(generator(rowcount=> end_month::int - start_month::int + 1))) 
 
,cte2(month_id,statement) as

(select 0,
        concat('create or replace temporary view master_', 
                split_part(table_pattern,'.',-1), 
                start_month,
                '_', 
                end_month,
                ' as ')
 union all
 select month_id,
        concat('select * from ', 
                table_pattern,
                month_id, 
                case when month_id=end_month::int then ';' else ' union all ' end)
 from cte1)

select listagg(statement, '\n') within group (order by month_id) as create_view_statement
from cte2
$$;

PROCEDURE FOR EXECUTING THE OUTPUT OF THE UDF ABOVE

create or replace procedure sandbox.public.create_view(TABLE_PATTERN varchar, START_MONTH varchar,END_MONTH varchar)
returns varchar not null
language Javascript
execute as caller
as

$$
sql_command = 'select * from table(sandbox.public.define_view(:1, :2, :3))';
var stmt = snowflake.createStatement({sqlText: sql_command ,binds: [TABLE_PATTERN, START_MONTH, END_MONTH]}).execute();
stmt.next();
var ddl = stmt.getColumnValue(1);
var run=snowflake.createStatement({sqlText: ddl}).execute();
run.next();
var message=run.getColumnValue(1);
return "Temporary " + message;
$$; 

USAGE DEMO

set table_pattern ='sandbox.public.table_';
set start_month ='1';
set end_month = '12';
set master_view='master_'||split_part($table_pattern,'.',-1)||$start_month||'_'||$end_month;

call create_view($table_pattern, $start_month, $end_month);

select top 100 *
from identifier($master_view);

Answer 2

Can you not create a view on top of these 12 tables. The view will be an union of all these tables.

Based on the comments below, I further elaborated my answer. please try this approach. It will provide better performance when your table is large. Partitioning it will improve performance. This is based on real experience.

CREATE TABLE SALES_2000 (REGION VARCHAR, UNITS_SOLD NUMBER);
CREATE TABLE SALES_2001 (REGION VARCHAR, UNITS_SOLD NUMBER);
CREATE TABLE SALES_2002 (REGION VARCHAR, UNITS_SOLD NUMBER);
CREATE TABLE SALES_2003 (REGION VARCHAR, UNITS_SOLD NUMBER);

INSERT INTO SALES_2000 VALUES('ASIA', 25);
INSERT INTO SALES_2001 VALUES('ASIA', 50);
INSERT INTO SALES_2002 VALUES('ASIA', 55);
INSERT INTO SALES_2003 VALUES('ASIA', 65);

CREATE VIEW ALL_SALES AS
SELECT * FROM SALES_2000
UNION
SELECT * FROM SALES_2001
UNION
SELECT * FROM SALES_2002
UNION
SELECT * FROM SALES_2003;


SELECT * FROM ALL_SALES WHERE UNITS_SOLD = 25;

Answer 3

Here's a stored procedure that will insert into master_table_1_12 from selects on table_1 through table_12. Modify as required:

create or replace procedure FILL_MASTER_TABLE()
returns string
language javascript
as
$$
    var rows = 0;
    for (var i=1; i<=12; i++) {
        rows += insertRows(i);
    }
    return rows + " rows inserted into master_table_1_12.";

// End of main function

function insertRows(i) {

sql = 
`insert into master_table_1_12 
select * 
from table_${i};`;

return doInsert(sql);
}

function doInsert(queryString) {
    var out;
    cmd1 = {sqlText: queryString};
    stmt = snowflake.createStatement(cmd1);
    var rs = stmt.execute();;
    rs.next();
    return rs.getColumnValue(1);
}
$$;

call fill_master_table();

By the way, if you don't have any processing to do and just need to consolidate the tables, you can do something like this:

insert into master_table_1_12 
select * from table_1
    union all
select * from table_2
    union all
select * from table_3
    union all
select * from table_4
    union all
select * from table_5
    union all
select * from table_6
    union all
select * from table_7
    union all
select * from table_8
    union all
select * from table_9
    union all
select * from table_10
    union all
select * from table_11
    union all
select * from table_12
;