Procedure to dynamically union tables

Title

JS Procedure to create result as a union of multiple tables

Description

Create new table as a union of many tables using a single procedure call.
It takes all tables in current schema and creates a result table namedUNION_TABLES.

Very useful in data transformation scenarios for example in Keboola Connection when you need to merge many tables with same schema.

create or replace procedure union_tables()
  returns string not null
  language javascript
  as
  '
    var tables_to_union = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE=''BASE TABLE'';";
    var statement1 = snowflake.createStatement( {sqlText: tables_to_union } );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time...
    var union = ''CREATE OR REPLACE TABLE union_tables AS \\n'';
    var wr_union = false;
    while (result_set1.next())  {
       var tb_name = result_set1.getColumnValue(1);
       if (wr_union) {union += ''\\n UNION ALL \\n'';}
       wr_union = true;

       union += "SELECT ''" + tb_name+ "'' AS table_name, \\"Year\\", \\"Month\\", \\"Profit_Center\\", \\"Local_Account\\", \\"Cost_Element\\", \\"Amount\\", \\"Currency\\", \\"Partner_company\\" FROM \\"" + tb_name +"\\"";
       }
  var union_statement = snowflake.createStatement( {sqlText: union} );
  union_statement.execute();
  return union;
  '
  ;

CALL union_tables();

Usage

  1. Modify SQL fetching tables to union in tables_to_union variable if needed
  2. Modify select statement / column list matching your table schema e.g. union += "SELECT ''" + tb_name+ "'' AS table_name, \\"Year\\", \\"Month\\", \\"Profit_Center\\", \\"Local_Account\\", \\"Cost_Element\\", \\"Amount\\", \\"Currency\\", \\"Partner_company\\" FROM \\"" + tb_name +"\\"";