user3669092 user3669092 - 9 days ago 4
SQL Question

Adding New Records from one table with existing and new records to another table in SQL

I'm trying to to append data to a table that contains all the data up to this point. Every week I will be pulling in the new data (which will contain data already existing in the All table) and adding the new records. I added a few test data to the temp table where the generic, material num, etc. are all different but when I run this query it still says it is adding 0 records. Please help.

INSERT INTO ExtWafersAll ( generic, [material number], description, vendor, [net price], [std price], NumberOfDups )
SELECT
ExtWafersTemp.generic,
ExtWafersTemp.[material number],
ExtWafersTemp.description,
ExtWafersTemp.vendor,
ExtWafersTemp.[net price],
ExtWafersTemp.[std price],
ExtWafersTemp.NumberOfDups
FROM ExtWafersTemp
RIGHT JOIN ExtWafersAll
ON (ExtWafersAll.NumberOfDups = ExtWafersTemp.NumberOfDups)
AND (ExtWafersAll.[std price] = ExtWafersTemp.[std price])
AND (ExtWafersAll.[net price] = ExtWafersTemp.[net price])
AND (ExtWafersAll.vendor = ExtWafersTemp.vendor)
AND (ExtWafersAll.description = ExtWafersTemp.description)
AND (ExtWafersAll.[material number] = ExtWafersTemp.[material number])
AND (ExtWafersAll.generic = ExtWafersTemp.generic)
WHERE
ExtWafersTemp.vendor <> ExtWafersAll.vendor
OR ExtWafersTemp.description <> ExtWafersAll.description
OR ExtWafersTemp.[material number] <> ExtWafersAll.[material number]
OR ExtWafersTemp.generic <> ExtWafersAll.generic;


So for example in ExtWafersTemp we have:


Generic Material Number Description Vendor Net Price Std Price
j2151 sjkdga215 xxx125125 TMA 12 14
asdg asgasg aggsggs asg 15 18


And then in ExtWafersAll:


Generic Material Number Description Vendor Net Price Std Price
j2151 sjkdga215 xxx125125 TMA 12 14


I can't figure out how to add the new record thats in the temp to the all file

rsc rsc
Answer

Maybe this would suit your need:

insert into ExtWafersAll ( generic, [material number], description, vendor, [net price], [std price], NumberOfDups )
select generic, [material number], description, vendor, [net price], [std price], NumberOfDups
from ExtWafersTemp
except
select generic, [material number], description, vendor, [net price], [std price], NumberOfDups
from ExtWafersAll;

In above snippet you add records from ExtWafersTemp table which are not present in ExtWafersAll table. Is this what are you trying to achieve?

About "except" operator you could read here: http://en.wikipedia.org/wiki/Set_operations_%28SQL%29

UPDATE

As it occurred to be MS Access problem you could try to test this:

SELECT
  ExtWafersTemp.generic,
  ExtWafersTemp.[material number],
  ExtWafersTemp.description,
  ExtWafersTemp.vendor,
  ExtWafersTemp.[net price],
  ExtWafersTemp.[std price],
  ExtWafersTemp.NumberOfDups
FROM ExtWafersAll RIGHT JOIN ExtWafersTemp 
ON (ExtWafersAll.NumberOfDups = ExtWafersTemp.NumberOfDups
    AND ExtWafersAll.[std price] = ExtWafersTemp.[std price]
    AND ExtWafersAll.[net price] = ExtWafersTemp.[net price]
    AND ExtWafersAll.vendor = ExtWafersTemp.vendor
    AND ExtWafersAll.description = ExtWafersTemp.description
    AND ExtWafersAll.[material number] = ExtWafersTemp.[material number]
    AND ExtWafersAll.generic = ExtWafersTemp.generic)
WHERE ExtWafersAll.NumberOfDups is null
  AND ExtWafersAll.[std price] is null
  AND ExtWafersAll.[net price] is null
  AND ExtWafersAll.vendor is null
  AND ExtWafersAll.description is null
  AND ExtWafersAll.[material number] is null
  AND ExtWafersAll.generic is null

Genarally it is a following pattern (in example there is a primary key field - id):

select tt.id
from tableall t right join tabletemp tt 
  on (t.id = tt.id)
where t.id is null

Hope that it helps.

Comments