Snowflake multiple selects within insert into is returning an error of unexpected value

Question

I am having the following query that will insert into a table:

INSERT INTO HUB_LOCATION (LOCATION_HKEY, LOAD_DT, RECORD_SRC, LOCATION_TYPE, LOCATION_VALUE)
SELECT 
        md5(CONCAT_WS('', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK)) AS BLOCK_HKEY, CURRENT_TIMESTAMP(), 'ONA', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK
FROM TEMP_TABLE WHERE BLOCK_HKEY NOT IN (SELECT LOCATION_HKEY FROM HUB_LOCATION) AND EXISTS (SELECT BLOCK FROM TEMP_TABLE);

I need to insert another rows that have FACILITY_ID as well, so I tried to do the following:

INSERT INTO HUB_LOCATION (LOCATION_HKEY, LOAD_DT, RECORD_SRC, LOCATION_TYPE, LOCATION_VALUE)
 values 
        ( md5(CONCAT_WS('', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK)) AS BLOCK_HKEY, CURRENT_TIMESTAMP() as ts, 'SRC', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK FROM TEMP_TABLE WHERE BLOCK_HKEY NOT IN (SELECT LOCATION_HKEY FROM HUB_LOCATION) AND EXISTS (SELECT BLOCK FROM TEMP_TABLE)),
        ( md5(CONCAT_WS('', 'FACILITY_ID', OBJECT_CONSTRUCT(*):FACILITY_ID)) AS FACILITY_HKEY, CURRENT_TIMESTAMP() as ts, 'SRC', 'FACILITY', OBJECT_CONSTRUCT(*):FACILITY_ID FROM TEMP_TABLE WHERE FACILITY_HKEY NOT IN (SELECT LOCATION_HKEY FROM HUB_LOCATION) AND EXISTS (SELECT FACILITY_ID FROM TEMP_TABLE));

It produced an error:

SQL compilation error: syntax error line 3 at position 65 unexpected 'AS'. syntax error line 3 at position 80 unexpected 'CURRENT_TIMESTAMP'. syntax error line 3 at position 100 unexpected 'as'.

I can do each field separately but it is preferable to do it in one INSERT INTO as in future we might have more types of locations.

Are you missing a SELECT in the second part? INSERT... values( SELECT md5(... — Valentin Sky, Mar 11 '21 at 09:21
You can do it using the keyword UNION. Just do it similar to your first query and add UNION [second SELECT query] — Valentin Sky, Mar 11 '21 at 09:49
@ValentinSky thanks for the help, I did it already and posted my own answer. — alim1990, Mar 11 '21 at 10:17

score 0 · Answer 1 · answered Mar 11 '21 at 09:48

I found my own answer using UNION ALL:

INSERT INTO HUB_LOCATION (LOCATION_HKEY, LOAD_DT, RECORD_SRC, LOCATION_TYPE, LOCATION_VALUE)
SELECT md5(CONCAT_WS('', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK)) AS BLOCK_HKEY, CURRENT_TIMESTAMP(), 'ONA', 'BLOCK', OBJECT_CONSTRUCT(*):BLOCK FROM TEMP_TABLE WHERE BLOCK_HKEY IS NOT NULL AND BLOCK_HKEY NOT IN (SELECT LOCATION_HKEY FROM HUB_LOCATION) AND EXISTS (SELECT OBJECT_CONSTRUCT(*):BLOCK FROM TEMP_TABLE)
UNION ALL
SELECT md5(CONCAT_WS('', 'FACILITY', OBJECT_CONSTRUCT(*):FACILITY_ID)) AS FACILITY_HKEY, CURRENT_TIMESTAMP(), 'ONA', 'FACILITY', OBJECT_CONSTRUCT(*):FACILITY_ID FROM TEMP_TABLE WHERE FACILITY_HKEY IS NOT NULL AND FACILITY_HKEY NOT IN (SELECT LOCATION_HKEY FROM HUB_LOCATION) AND EXISTS (SELECT OBJECT_CONSTRUCT(*):FACILITY_ID FROM TEMP_TABLE);

Snowflake multiple selects within insert into is returning an error of unexpected value

1 Answers1