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;
Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds