CODEWITHSUNDEEP
snowflake-cloud-data-platform
RDK
RDK

Reputation: 153

Snowflake | Insert overwrite fails with Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got FLOAT for column

Trying to insert overwrite data from one table with x+y columns into table with x+z columns and getting error SQL compilation error: Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got FLOAT for column DATEMODIFIED. In this case data type for DATEMODIFIED on both tables is TIMESTAMP_NTZ(9). Query goes like below

insert overwrite into tgt_table
select x1,x2,x3, DATEMODIFIED,  null as z1, null as z2 from 
(select x1,x2,x3, DATEMODIFIED from src_table)

Note:

insert overwrite into tgt_table
select x1,x2,x3, DATEMODIFIED::TIMESTAMP_NTZ  as DateModified,  null as z1, null as z2 from 
(select x1,x2,x3, DATEMODIFIED::TIMESTAMP_NTZ  as DateModified from src_table)

Upvotes: 1

Views: 1427

Answers (1)

Michael Golos
Michael Golos

Reputation: 2049

I suspect that the order of the columns in the target table is different. Try to define target columns in your command like this:

insert overwrite into tgt_table (x1,x2,x3, DATEMODIFIED, z1, z2)
select x1,x2,x3, DATEMODIFIED,  null as z1, null as z2 from 
(select x1,x2,x3, DATEMODIFIED from src_table)

Reference: INSERT target_col_name

Upvotes: 2

Related Questions