[SOLVED] Insert into table from another table where the records don't exist

Insert into table from another table where the records don't exist

I'm trying to figure out how to insert into an existing table (tbl01) from a temporary table (temp) where the records do not already exist in the existing table (tbl01). I hope that makes sense. I'm basically, trying to update a table with records that have occurred since the last update of the table. Here's my code so far:

insert into tbl01
(sale_store, sale_dt, sale_register, sale_trans)
select distinct
sale_store, sale_dt, sale_register, sale_trans
from temp
where NOT EXISTS (select * from tbl01)

The issue that I'm having is that it runs, but does not put any new records into the table - there should be be lots of new records. I'm sure it's something small and stupid I'm missing. I used this post as my guide: How to avoid duplicates in INSERT INTO SELECT query in SQL Server?

Thank you in advance!

Solution

The problem is that your inner query does not depend on the temp table in any way. Basically, what you wrote was "insert into tbl01 if no records exists in tbl01". To fix it you need to add a where clause to the query inside the exists:

insert into tbl01
(sale_store, sale_dt, sale_register, sale_trans)
select distinct
sale_store, sale_dt, sale_register, sale_trans
from temp
where NOT EXISTS (
    select * 
    from tbl01
    where temp.sale_store = tbl01.sale_store 
    and temp.sale_dt = tbl01.sale_dt
    and temp.sale_register = tbl01.sale_register
    and temp.sale_trans = tbl01.sale_trans)