Is there a Snowflake equivalent for T-SQL INSERT INTO [Tablename] EXEC [StoredProcedure]?

user_16973399 0
Viewed 59

Using T-SQL in SQL Server I can insert the results of a stored procedure into a table using this syntax:

INSERT INTO <TableName> 
EXEC <StoredProcedureName>
GO

Presently there is already a Stored Procedure that returns the data I need so I'd rather not re-invent the existing query to get that data into a table; but I'm not that knowledgeable with the capabilities of Snowflake in this respect.

Is there a similar Snowflake syntax I could use to achieve the same result?

The closest I could find was the Copy Into command but I haven't been able to successfully use it in this manner.

snowflake-cloud-data-platform
1 Answers

It is possible to use RESULT_SCAN to operate on stored procedure output:

CALL <stored_procedure>;

INSERT INTO <table_name>(....)
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Sample:

CREATE OR REPLACE TABLE tab(i INT);

CREATE OR REPLACE PROCEDURE dummy()
RETURNS TABLE(col INT)
LANGUAGE SQL
AS
$$
 DECLARE
        res RESULTSET default (select 1 AS c);
  BEGIN
        RETURN TABLE(res);
  END;
$$;

CALL dummy();


INSERT INTO t(i)
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

SELECT * FROM t;
-- i
-- 1
user_50708790
Related