I have created a stored procedure that can convert all views from a schema into tables in another schema but it simply does not run in Snowflake.
CREATE OR REPLACE PROCEDURE COPY_VIEWS_TO_TABLES()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
SOURCE_SCHEMA STRING;
TARGET_SCHEMA STRING;
CURRENT_VIEW STRING;
CREATE_TABLE_SQL STRING;
BEGIN
-- Assign values to the source and target schemas
SOURCE_SCHEMA := 'SOURCE.STAGE';
TARGET_SCHEMA := 'TARGET.STAGE.TABLES';
-- Iterate through all views in the source schema
FOR VIEW_NAME IN (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = SOURCE_SCHEMA
)
DO
-- Assign the current view name
CURRENT_VIEW := VIEW_NAME;
-- Construct the SQL statement to create a table from the view
CREATE_TABLE_SQL :=
'CREATE TABLE "' || TARGET_SCHEMA || '"."' || CURRENT_VIEW || '" AS
SELECT * FROM "' || SOURCE_SCHEMA || '"."' || CURRENT_VIEW || '"';
-- Execute the dynamic SQL statement
EXECUTE IMMEDIATE CREATE_TABLE_SQL;
END FOR;
RETURN 'All views have been copied successfully!';
END;
$$;
Please advise.
If I change your SQL to:
CREATE OR REPLACE PROCEDURE COPY_VIEWS_TO_TABLES()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
SOURCE_SCHEMA STRING;
TARGET_SCHEMA STRING;
CURRENT_VIEW STRING;
CREATE_TABLE_SQL STRING;
c1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'SO';
BEGIN
-- Assign values to the source and target schemas
SOURCE_SCHEMA := 'SO.SO';
TARGET_SCHEMA := 'SO._TO';
OPEN c1;
-- Iterate through all views in the source schema
FOR VIEW_NAME IN c1
DO
-- Assign the current view name
CURRENT_VIEW := VIEW_NAME.TABLE_NAME;
-- Construct the SQL statement to create a table from the view
CREATE_TABLE_SQL :=
'CREATE TABLE ' || TARGET_SCHEMA || '."' || CURRENT_VIEW || '" AS
SELECT * FROM ' || SOURCE_SCHEMA || '."' || CURRENT_VIEW || '"';
-- Execute the dynamic SQL statement
-- for debuging this SQL
--RETURN :CREATE_TABLE_SQL;
EXECUTE IMMEDIATE CREATE_TABLE_SQL;
END FOR;
RETURN 'All views have been copied successfully!';
END;
$$;
it will copy from db.schema so.so
to database.schema so._to
To get it working I run your given SQL, and got the error:
Error:'EXPRESSION_ERROR' on line 14 at position 21 : SQL compilation error:
error line 4 at position 29 (line 45) invalid identifier 'SOURCE_SCHEMA' (line 45)
so I changed the SQL to:
FOR VIEW_NAME IN (
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = :SOURCE_SCHEMA
)
which gave:
092234 (P0000): Uncaught exception of type 'STATEMENT_ERROR' on line 16 at position 4 : Expression in variant for loop does not evaluate to an array or an object
then I read the FOR doc's and noticed the examples are using a cursor.
so swapped to that, then needed to change CURRENT_VIEW := VIEW_NAME.TABLE_NAME;
Then I changed how you were quoting the database name & schema name, as these should not need to be quoted as "nobody would use special tokens in these, right?"
And I was returning the SQL, so I could run it outside the SP to test it.