omrest.blogg.se

Replace(sql_template, 'SCHEMA', table_info.TABLE_SCHEMA) AS sql_with_schema, $$SELECT *, 'TABLE_NAME' as table_name FROM SCHEMA."TABLE_NAME"$$ AS sql_template, *create a table with a single row of text that contains the query you want to pass to the stored procedure*/ĭrop table if exists TARGETSCHEMA._SQL_COMMAND_RESTOCK_INV_REPORT Ĭreate table TARGETSCHEMA._SQL_COMMAND_RESTOCK_INV_REPORT asĪND TABLE_NAME ilike 'amazon_mws_%^_RestockInventoryReport' ESCAPE '^' Here is what this process looks like for us in SQL. StmtEx = snowflake.createStatement(cmd2_dict) Įssentially, what this person was doing is creating a SQL statement to “delete all tables that end with “TEST”” and then passing that statement to a stored procedure to execute. * Note that this procedure will always use the column named "SQL_COMMAND" * Stored procedure to execute multiple SQL statements generated from a SQL query create or replace procedure DynmanicTableUnion(sqlCommand String) You can drop this SQL procedure into a Mitto SQL job and run it. Test,union_single_table_b,1,this is the first row of table b Test,union_single_table_a,2,this is the second row of table a Test,union_single_table_a,1,this is the first row of table a Here’s the resulting data: table_schema,table_name,id,comment USING QUOTE_IDENT(_query.table_schema), QUOTE_IDENT(_query.table_name) 'INSERT INTO test.union_tables_with_proc (table_schema, table_name, id, comment)įrom ' || QUOTE_IDENT(_query.table_schema) || '.' || QUOTE_IDENT(_query.table_name) RAISE INFO 'The schema is %, the table is %', _query.table_schema, _query.table_name DOĭROP TABLE IF EXISTS test.union_tables_with_proc ĬREATE TABLE IF NOT EXISTS test.union_tables_with_proc (ĪND table_name LIKE 'union_single_table_%' STEP 2 - Run SQL procedure to dynamically union these tables. DROP TABLE IF EXISTS test.union_single_table_aĬREATE TABLE IF NOT EXISTS test.union_single_table_a (ĭROP TABLE IF EXISTS test.union_single_table_bĬREATE TABLE IF NOT EXISTS test.union_single_table_b ( NOTE: These tables have the same number of columns and those columns’ data types match. STEP 1 - Create two tables to later union.

Here’s a simple example of my understanding of what you are trying to do.Īll of the below SQL is using PostgreSQL.