sqlamazon-s3snowflake-cloud-data-platformstage

Populate snowflake table with default values without selecting default column values from the file data


I am trying to load a table (drop table and load the data - similar to truncate and load) dynamically. Let us assume that table needs to have 4 fields, ID, Name, SeqNo, and DtTimeStamp.

The data is being selected from an externally staged csv\text file that has only two fields (ID and Name). The below query gives an error for the nonmatching of a number of columns. How to resolve that issue?

CREATE OR REPLACE TABLE SOMETABLENAME(ID NUMBER(38,0), Name VARCHAR(255), SeqNo NUMBER(38,0) NOT NULL AUTOINCREMENT, DtTimeStamp TIMESTAMP_NTZ(9) NOT NULL DEFAULT CURRENT_TIMESTAMP()) AS SELECT A.$1 AS ID, A.$2 AS Name FROM @EXTERNALSTAGE/SOME_FILE.CSV A;

If you carefully look at the above SQL statement, my table has two extra fields that need to be auto-populated for every row it loads. But I am unable to make it work?

Any suggestions are highly appreciated.

Thanks in Advance! Sathya


Solution

  • CREATE TABLE … AS SELECT (CTAS)

    CREATE TABLE <table_name> ( <col1_name> , <col2_name> , ... ) AS SELECT ...
    

    The number of column names specified must match the number of SELECT list items in the query; the types of the columns are inferred from the types produced by the query.

    To resolve it, CTAS and INSERT INTO could be two separate steps:

    CREATE OR REPLACE TABLE SOMETABLENAME(
        ID NUMBER(38,0),
        Name VARCHAR(255),
        SeqNo NUMBER(38,0) NOT NULL AUTOINCREMENT,
        DtTimeStamp TIMESTAMP_NTZ(9) NOT NULL DEFAULT CURRENT_TIMESTAMP()
    );
    
    -- here INSERT/SELECT have matching column list
    INSERT INTO SOMETABLENAME(ID, Name)
    SELECT A.$1 AS ID, A.$2 AS Name FROM @EXTERNALSTAGE/SOME_FILE.CSV A;