[SQL] Populating a table based on the contents of an other one without the use of cursors

I am using the Snowflake platform.

I would like to know if it is possible to populate a table based in the content of an other table without the use of cursors. The example given is the most minimal I could come up with in regards to the problem I am facing (meaning the procedure cannot be replaced by a function).

Having this setup:

create or replace table test_1 (num integer);
insert into test_1 values (1), (2), (3);

create or replace table test_2 (num integer, doubled string);

create or replace procedure double_val(val integer)
returns integer
as
begin
return val * 2;
end;


I would like to populate the table called test_2 based on the contents of test_1, using the procedure to specify the value for the column name doubled.

Meaning that for the current state of test_1, I would like table test_2 to look like this:

|---------------|
| num | doubled |
|----------------
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
|---------------|


Where the result of the doubled column comes from a call to double_val().

Is this possible without the use of a cursor, preferably in plain SQL syntax?

Continue reading...