3

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!

JNevill
  • 46,980
  • 4
  • 38
  • 63
DJDJ23
  • 139
  • 1
  • 2
  • 12
  • How do you determine a distinct record in your `tbl01`. Is it `sales_store`, or multiple fields? `NOT EXISTS (SELECT ...)` runs the select statement and if it has ANY records then `NOT EXISTS` fails, so you either need a correlated subquery, or to use a ' NOT IN (SELECT FROM...)` – JNevill May 24 '17 at 14:31
  • Those four fields together create a 'distinct' record. So I need to include all four of those fields as something like: where not exists (select sale_store, sale_dt, sale_register, sale_trans from tbl01 – DJDJ23 May 24 '17 at 14:35
  • 1
    If your target is created as a SET table and your PK includes all columns you can simply INSERT/SELECT with no WHERE, duplicates rows are silently discarded by a SET table. – dnoeth May 24 '17 at 15:16

1 Answers1

8

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)
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121