01 April 2019 · n-pacha ·       Add to Favorites   Report

How to implement an Insert which has a subquery in Snowflake?

Snowflake supports insert statements with only one select phrase in it. You cannot embed a select in an Insert command. For instance, if you  want to insert into a table and choose a few values from other tables, like below,

insert into Employee values('1', (select max(b) +1 from Employee), 'val3', 'val4');

it will fail in snowflake with the below error:

SQL compilation error: Invalid expression xxxx in VALUES clause

The Solution:

You will need to rewrite such queries in snowflake using select and/or joins,

insert into Employee select '1', max(b) +1, 'val3', 'val4' from Employee;

If you need to pull data from a different table, you will need to use joins to write proper select insert query as Snowflake doesn't support embeds:

insert into emp (id,first_name,last_name,city,postal_code,ph)
select a.id,a.first_name,a.last_name,a.city,a.postal_code,b.ph
from emp_addr a
inner join emp_ph b on a.id = b.id;

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too