CODEWITHSUNDEEP
snowflake-cloud-data-platformsql-insert
Aaron
Aaron

Reputation: 1455

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.

Upvotes: 1

Views: 682

Answers (3)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

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

Upvotes: 1

Anand
Anand

Reputation: 564

You can try using not exists clause :

select
    patient_id,
    npi,
    diagnosis_code,
    from_date,
    to_date
from
    table t1
where
    not exists (
        select
            1
        from
            table t2
        where
            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
    );

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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;

Upvotes: 1

Related Questions