FoolzRailer FoolzRailer - 4 months ago 16
SQL Question

Append query should only append if value not present

I'm trying to run an append query, but it should only run if there isn't already a value present in order to avoid duplicates.

I heard you can do this with a Where clause, but not sure how to implement it, as what I've tried so far (see code below) isn't really giving me the right result.

It needs to check if there already exists a value in Saneringsmetodekode, if there is one, it should do nothing.

INSERT INTO NySaneringsData ( DelledningsID, SaneringsmetodeKode )
SELECT SaneringsprojektImport.DelledningsID, SaneringsprojektImport.SaneringsmetodeKode
FROM NySaneringsData RIGHT JOIN SaneringsprojektImport ON NySaneringsData.DelledningsID = SaneringsprojektImport.DelledningsID
WHERE (((SaneringsprojektImport.SaneringsmetodeKode) Is Not Null) AND ((Exists (SELECT * FROM NySaneringsData WHERE NySaneringsData.SaneringsmetodeKode = SaneringsprojektImport.SaneringsmetodeKode))=False))
ORDER BY SaneringsprojektImport.DelledningsID;


Any help would be much appreciated.

Answer

Use this construction:

INSERT INTO
SELECT
FROM
WHERE NOT EXISTS

Referring to this similar question on stackoverflow:

Insert INTO NOT EXISTS SQL access

You can drop the ORDER BY as it's irrelevant (tables have no ordering by nature).

Based on your input, it's the combination of SaneringsmetodeKode and DelledningsID that uniquely identifies your records. So in the sub query you should have two conditions, one on DelledningsID and one on SaneringsmetodeKode. Try this:

INSERT INTO NySaneringsData (DelledningsID, SaneringsmetodeKode )
SELECT SaneringsprojektImport.DelledningsID, SaneringsprojektImport.SaneringsmetodeKode
FROM SaneringsprojektImport 
WHERE NOT EXISTS
(
SELECT * 
FROM NySaneringsData 
WHERE NySaneringsData.SaneringsmetodeKode = SaneringsprojektImport.SaneringsmetodeKode
AND NySaneringsData.DelledningsID = SaneringsprojektImport.DelledningsID
);