Snowflake Unable to insert multiple rows using insert into having md5 field

I am adding multiple rows using INSERT INTO table VALUES as follows:

insert into hub values 
 (md5(md5('test1')) as enc,  CURRENT_TIMESTAMP() as ct, 'DATA_SOURCE_1', 'test1'),
 (md5(md5('test2')) as enc,  CURRENT_TIMESTAMP() as ct, 'DATA_SOURCE_1', 'test2')
;

And I am getting the following error:

SQL compilation error: syntax error line 2 at position 19 unexpected ‘as’. syntax error line 2 at position 28 unexpected ‘CURRENT_TIMESTAMP’. syntax error line 2 at position 50 unexpected ”DATA_SOURCE_1”. syntax error line 3 at position 19 unexpected ‘as’. syntax error line 3 at position 28 unexpected ‘CURRENT_TIMESTAMP’. syntax error line 3 at position 50 unexpected ”DATA_SOURCE_1”.

I tried to remove the as from the subqueries but I got the following error:

SQL compilation error: Invalid expression [MD5(MD5(‘test’))] in VALUES clause

I know that md5 should return a value not an expression that’s why I used AS but it didn’t work.

Answer

That seems like a bug to me and it’s kind of similar to this question. You can write the insert in a different way however to achieve the same result:

insert into hub
    select md5(md5('test1')), CURRENT_TIMESTAMP(), 'DATA_SOURCE_1', 'test1'
union all
    select md5(md5('test2')), CURRENT_TIMESTAMP(), 'DATA_SOURCE_1', 'test2'

or alternatively like the following, which may be more concise if you have many rows in the VALUE part:

insert into hub
select
    md5(md5(column1)),
    column2,
    column3,
    column4
from
values
       ('test1', CURRENT_TIMESTAMP(), 'DATA_SOURCE_1', 'test1'),
       ('test2', CURRENT_TIMESTAMP(), 'DATA_SOURCE_1', 'test2')