CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
HoneyBadger786
HoneyBadger786

Reputation: 89

Snowflake: Trying to insert into a table with values generated via multiple selects

I am trying to migrate a query from Redshift to snowflake. This query is used to populate a row in a table and the values are generated from various other tables. This worked fine in redshift, but I have been trying to check snowflake documentation and can't find if it is supported in anyway in snowflake. Anyone has any idea how this could be rewritten

insert into
    etl_audit_table
    (
        batch_id,
        batch_run_date,
        table_name,
        staging_row_count,
        dwh_load_type
    )
    values
    (
    (   select
            batch_id
        from
            etl_cntrl.batch_history_table
        where
            batch_status='running'
    )
    ,
    current_date,
    'tablename',
    (   select
            count(*)
        from
            table
        where
            lastmodifieddate between '2021-07-31 00:00:00' and '2021-08-17 00:00:00'
    ),
    'Slow Changing Dimension'
    )

When trying to execute on snowflake I get the following error

11:46:54 FAILED [INSERT - 0 rows, 0.658 secs] [Code: 2014, SQL State: 22000] SQL compilation error: Invalid expression [(SELECT 1 AS "BATCH_ID" FROM TABLE (GENERATOR)ROWCOUNT => 1, rowCount => 1) GENERATOR)] in VALUES clause

Upvotes: 0

Views: 2746

Answers (1)

HoneyBadger786
HoneyBadger786

Reputation: 89

I was able to make it work by rewriting the query as follows

insert into
    etl_audit_table
    (
        batch_id,
        batch_run_date,
        table_name,
        staging_row_count,
        dwh_load_type
    )
    select
    (   select
            batch_id
        from
            etl_cntrl.batch_history_table
        where
            batch_status='running'
    )
    ,
    current_date,
    'tablename',
    (   select
            count(*)
        from
            table
        where
            lastmodifieddate between '2021-07-31 00:00:00' and '2021-08-17 00:00:00'
    ),
    'Slow Changing Dimension'

Upvotes: 1

Related Questions