SQL INSERT missing rows from Table A to Table B

Question

I'm trying to insert rows into table 'Data' if they don't already exist.

For each row in Export$, I need the code to check 'Data' for rows that match both Period (date) and an ID (int) - if the rows don't already exist then they should be created.

I'm pretty sure my 'NOT EXISTS' part is wrong - what's the best way to do this? Thanks for all your help

    IF NOT EXISTS (SELECT * FROM Data, Export$ WHERE Data.ID = Export$.ID AND Data.Period = Export$.Period)
    INSERT INTO Data (Period, Performance, ID)
    SELECT Period, [Return], [ID] FROM Export$

Solution 1

try this:

INSERT INTO Data (Period, Performance, ID)
SELECT Period, [Return], [ID] 
FROM Export$ e
where not exists (
select *
from Data
where  ID = e.ID and Period = e.Period)

Solution 2

try something like, will need tweaking to fit your tables

insert into data
select * from export
left join data on data.id = export.id
                     and data.period = export.period
where data.id is null

Comments

[Ishimwe Aubain Consolateur]

Although this might work, if you have tons of data your query will take forever. The joins would be a good way to go. You can avoid the inner join as it also may suffer the same performance issue and use Left or right join (depending on your arrangement) and add a where condition to exclude the joining param with null