可以使用Snowflake脚本块：
从此线程跟进
with a as (
select * from hubspot.information_schema.tables
where table_catalog = 'HUBSPOT' AND TABLE_SCHEMA = 'MONGODB' and table_name != '_SDC_REJECTED' and table_type = 'BASE TABLE'
),
b as (
select * ,
$$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
replace(t,'TABLE_NAME',table_name) as sql
from a
)
, test as (
select listagg(sql,'nUNION ALLn') within group (order by table_schema, table_catalog)
from b
)
EXECUTE IMMEDIATE SELECT * FROM test
;
我已经设法创建了一个select语句，该语句将特定模式中的一组表联合起来。然后我该如何执行这个字符串？
我尝试过EXECUTE IMMEDIATE，但一直收到以下错误：'语法错误：意外的'EXECUTE'。(第20行('
DECLARE
res RESULTSET;
sqlQuery TEXT;
BEGIN
WITH a as (
select *
from hubspot.information_schema.tables
where table_catalog = 'HUBSPOT'
AND TABLE_SCHEMA = 'MONGODB'
and table_name != '_SDC_REJECTED'
and table_type = 'BASE TABLE'
),b as (
select * ,
$$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
replace(t,'TABLE_NAME',table_name) as sql
from a
), test as (
select listagg(sql,'nUNION ALLn')
within group (order by table_schema, table_catalog) AS query
from b
)
SELECT query
INTO :sqlQuery
FROM test;
res := (EXECUTE IMMEDIATE :sqlQuery);
RETURN TABLE(res);
END;
相关：SELECT。。。进入。。。并立即执行
另一个选项是会话变量(尽管字符串的长度限制为256字节(：
SET sqlQuery = (SELECT ... );
-- SELECT query should return single column/row
EXECUTE IMMEDIATE $sqlQuery;