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);