SQL for append rows based on max date-CodePudding

This is more of a logic question as I am having a hard time wrapping my head around it.

Say I have table 1 that is truncated and populated everyday, and a time stamp column is added onto it. Everyday new records would be added to the table.

That table 1 is copied to table 2 initially, however on consequent runs I only want to add the new records from table 1 into table 2.

I know this will be a mixture of matching the columns and only importing the MAX DATES, however confused as to the actual logic of the query.

So in short I want to append only the latest rows from table 1 to table 2 based on the max date.

CodePudding user response：

If you want to sync the tables daily, you may just look for timestamp_column > current_Date.

If you want to get the max dates, you can write something like this:

INSERT INTO table2 (x,y,z, timestamp_column)
SELECT x,y,z, current_timestamp() FROM table1
WHERE timestamp_column > 
(SELECT IFNULL(MAX(timestamp_column), '0001-01-01' ) FROM table2);

On the other hand, I think Snowflake streams are a very good fit for this task:

https://docs.snowflake.com/en/user-guide/streams-intro.html

You can create an "Append-only" stream on table1, and use it as a source when synchronizing to table2.