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.
Advertisement
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')