sqlsnowflake-cloud-data-platform

Stored procedure in snowflake to convert all views to table


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.


Solution

  • 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.