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
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)
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