How can I iteratively "INSERT INTO {table} SELECT FROM".... for a range of dates?

Question

I am working on a building a Data Vault Temporal Point in Time (TPIT) table. For this I need to iterate through a list of dates and insert rows for each Date. Here is the SQL for one Date '2020-08-19' . I need to iterate over all dates from year 2000 to today.

What is the best way to implement this using SQL in Snowflake?

I can do this in Python, but I was looking for a SQL only way to do this, so that it can be embedded in an SQL refresh script that runs daily.

Thanks.

INSERT
    INTO
    MEMBERSHIP_TPIT
SELECT
    MEMBERSHIP_HUB.MEMBERSHIP_HASHKEY AS MEMBERSHIP_HASHKEY,
    '2020-08-19' AS SNAPSHOT_DATE,
    MEMBERSHIP_SAT.LOAD_DATE AS LOAD_DATE
FROM
    MEMBERSHIP_HUB INNER MEMBERSHIP_SAT ON
    (MEMBERSHIP_HUB.MEMBERSHIP_HASHKEY = MEMBERSHIP_SAT.MEMBERSHIP_HASHKEY
    AND '2020-08-19' BETWEEN START_DATE AND COALESCE(END_DATE,
    '9999-12-31'));

Answer 1

You can use below query along with WITH clause and refer DATE_RANGE as a table in your From clause and refer MY_DATE in place of '2020-08-19'.

WITH DATE_RANGE AS (
  SELECT DATEADD(DAY, -1*SEQ4(), CURRENT_DATE()) AS MY_DATE
    FROM TABLE(GENERATOR(ROWCOUNT => (365000) )) where my_date >='01-Jan-2000'
 ) 
SELECT * FROM DATE_RANGE