CODEWITHSUNDEEP
javascriptsqlstored-proceduressnowflake-cloud-data-platform
Adhi cloud
Adhi cloud

Reputation: 49

How to dynamically select columns from a given table in snowflake stored procedure?

I am trying to create a procedure that choose columns dynamically from two table. I have already hardcoded query which works fine.

#Existing query

create table mydb.result_table as 
select 
t1.id,
t1.name,
t1.place,
t1.product
t1.prize
t2.id,
t2.name,
t2.place,
t2.product,
t2.prize
from source_db.source_table t1
full outer join target_db_target_table t2 on 
t1.id=t2.id and t1.name=t2.name
where t1.place<>t2.place

Now i need to create a procedure which basically select column dynamically from given table name and join both with given keys

so something like below

CREATE OR REPLACE PROCEDURE result(source_db varchar, source_table VARCHAR, targt_db varchar, target_table VARCHAR, key_join varchar<not sure can pass list>, filter_col varchar )
returns string not null
language javascript
as
$$
var query= `create table mydb.result_table as 
select
t1.<all columns from source_db and source_table>
t2.<all columns from targt_db and target_table >
from <source_db and source_table> as t1
full outer join  <target_db_target_table> as t2
on <key_join> where <t1.filter_col <> t2.filter_col>
`
return 'success';
$$;

i don't see example of selecting columns dynamically for this kind of use case. Any solution to this?

Upvotes: 0

Views: 2144

Answers (2)

Eric Lin
Eric Lin

Reputation: 1520

Just query the INFORMATION_SCHEMA.COLUMNS view to access the table's columns and then you can build your query dynamically:

https://docs.snowflake.com/en/sql-reference/info-schema/columns.html

Upvotes: 0

NickW
NickW

Reputation: 9788

You need to query the information schema for the list of columns in each table and use the result to dynamically build your SQL statement.

You also need to be aware of the same column name existing in multiple tables as you obviously can’t have the same column appearing multiple times in a table - so you’ll need to dynamically rename columns as appropriate

Upvotes: 1

Related Questions