I have created table "A" include "Time", "User Name" and "Coordinate" from some existing tables in my database. How can I insert a new values into the created table "A" at every hour? I need something to check if there is a new data show up in my database that not exist in my created table "A", if yes, insert those new values into my table "A". If no, keep my table as it is and check back again after one hour... and so on
1 Answers
As mentioned in the comment above, this is fit for purpose for tasks and streams: https://community.snowflake.com/s/article/Using-Streams-and-Tasks-inside-Snowflake
You'll want to establish a stream on the table you want to check for new data, and a task that runs hourly to check the stream.
If no data, there's no processing - go back to sleep and wake up at next scheduled interval to check again.
If there is data, and if your insert can be accomplished in a single SQL statement, then that would be the body of your task - otherwise, if it requires more complex processing (multiple steps / multiple queries), you'll need to create a stored procedure and call it from the task.
Related