Insert into multiple tables using stream & tasks

Question

As per the official documentation, it depicts as though we can insert into multiple tables from a task. Which sounds inaccurate since

Once consumed the offsets of the stream are reset
It is possible to execute only one SQL statement from a task

am I missing something here? I want to be able to insert into 2 tables reading out of a stream through the task.

score 2 · Accepted Answer · answered Jun 05 '20 at 17:15

2

You can do this with a multi-table insert:

https://docs.snowflake.com/en/sql-reference/sql/insert-multi-table.html

answered Jun 05 '20 at 17:15

Greg Pavlik

10,089
2
12
29

score 2 · Answer 2 · answered Jun 08 '20 at 20:26

2

You can do this. Multi-table inserts are one way, but there is another.

The pointer in the stream is only advanced at the end of a transactions. Therefore, you can enclose multiple DML statements that read from the stream in a single transaction. Unfortunately, tasks can only execute a single SQL statements, so you will have to embed your queries in a stored procedure.

Hope this helps.

answered Jun 08 '20 at 20:26

Marius

208
5
14

This was my next move, but fortunately the accepted answer proved enough in my case – Somasundaram Sekar Jun 09 '20 at 08:36

Insert into multiple tables using stream & tasks

2 Answers2