Insert without creating duplicates

How to load unique data from one table into another table? For example, if I have columns - patient_id, npi, diagnosis_code, from_date, to_date etc in both tables, how do I make sure a duplicate record is not created in table 1 when the insert from table 2 happens.

In PostgreSQL, I can create a unique index on those columns, but Snowflake doesn't support that.


Using INSERT INTO SELECT EXCEPT:

INSERT INTO table_1(patient_id, npi, diagnosis_code, from_date, to_date)
SELECT patient_id, npi, diagnosis_code, from_date, to_date
FROM table_2
EXCEPT
SELECT patient_id, npi, diagnosis_code, from_date, to_date
FROM table_1;

Another solution is you can use the MERGE with the WHEN NOT MATCHED THEN INSERT clause:

merge into table_1 AS t1 using table_2 AS t2
    ON t1.patient_id = t2.patient_id 
        AND t1.npi= t2.npi 
        AND t1.diagnosis_code= t2.diagnosis_code
        AND t1.from_date= t2.from_date 
        AND t1.to_date = t2.to_date
    when not matched then 
        insert (patient_id, npi, diagnosis_code, from_date, to_date) 
            values (t2.patient_id,  t2.npi, t2.diagnosis_code, t2.from_date, t2.to_date);