Append query should only append if value not present

Question

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.

where would you like to use the where statement? The condition is not clear... please explain what do you want to do not only put your code — Zeina, Aug 08 '16 at 07:04
It should check if there is a value in SaneringsmetodeKode, if it exist it should not append the row. — FoolzRailer, Aug 08 '16 at 07:32

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

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
);

edited May 23 '17 at 11:51

Community

1
1

answered Aug 08 '16 at 07:06

Rene

1,095
1
8
17

Trying your suggestion, I've updated my code in the original post. But having difficulty with the where not exists clause. If something exists in NySaneringsData.SaneringsmetodeKode it should not update. Any idea, where I'm going wrong? – FoolzRailer Aug 08 '16 at 08:06
INSERT INTO NySaneringsData ( DelledningsID, SaneringsmetodeKode ) SELECT SaneringsprojektImport.DelledningsID, SaneringsprojektImport.SaneringsmetodeKode FROM NySaneringsData INNER JOIN SaneringsprojektImport ON NySaneringsData.DelledningsID = SaneringsprojektImport.DelledningsID WHERE NOT EXISTS (SELECT * FROM NySaneringsData WHERE NySaneringsData.SaneringsmetodeKode = SaneringsprojektImport.SaneringsmetodeKode); – Rene Aug 08 '16 at 08:17
Thank you very much for the help! Tried this and it almost gave me the correct result! For some reason a value that I can see is in SaneringsprojektImport and not in NySaneringsData is not getting recognized. Not quite sure why. It still appends the delledningsID to the table, causing duplicates of that. – FoolzRailer Aug 08 '16 at 08:26
Maybe due to trailing spaces (just a wild guess)? You could check on trailing spaces by comparing the number of characters in both tables using the Len function. – Rene Aug 08 '16 at 08:46
Didn't get to edit in time. For some reason, it returns only some values that need to be updated, not all of them as you can see in the link. So the where clause filters some out for some reason, seeing as they are not in NySaneringsData, they should be showing up in the append query. [QueryResult](http://imgur.com/kFZZhKb). Checked for trailing spaces, and everything seems to be in order, the length of the field corresponds to the number of digits. If I change the AND in my where clause to OR I seem to get them all, but this also includes Null values, which I would prefer not to have – FoolzRailer Aug 08 '16 at 09:00
Tried a few different combos that I could think off, I either get the result as shown in the link in my previous comment (Where i'm missing a few rows in my update query) Or I get everything including rows that are already in the table. – FoolzRailer Aug 08 '16 at 12:14
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. I've created a query that should work and added it to my previous answer. – Rene Aug 08 '16 at 20:34
Rene you are a genius! Thank you very kindly for the all help! It works like a charm! – FoolzRailer Aug 09 '16 at 05:43

score 1 · Answer 2 · edited May 23 '17 at 11:44

1

why not

SELECT 
  CASE 
     WHEN VALUE1 = VALUE2 THEN RESULT_VALUE1 
     WHEN VALUE1 = VALUE3 THEN RESULT_VALUE2
     ELSE RESULT_VALUE3
  END as DelledningID
WHERE xxxx

You can use CASE even in WHERE, ORER or GROUP BY clauses.

But, looking at the query, aren't you looking for a UPSERT-like query?

Regards,

Oak

edited May 23 '17 at 11:44

Community

1
1

answered Aug 08 '16 at 07:04

Oak_3260548

1,882
3
23
41

Upsert looks interesting, what i'm doing right now is first running an update query then and insert/append query. But the append query is giving me duplicates. Will give the different code suggestions a try. – FoolzRailer Aug 08 '16 at 07:32
Yes, I thought so. So use "upsert" like query. My recommendation is a method, where you will do an UPDATE (with specific ID in WHERE clause) and then check @@rowcount and if 0, then do INSERT. This is simple and has some advantages over other methods. You should find it in the thread linked above. – Oak_3260548 Aug 08 '16 at 07:36

score 1 · Answer 3 · 2016-08-08T09:48:36.583

1

I haven't tried this under access, but this is standard sql and should work:

INSERT INTO NySaneringsData ( DelledningsID, SaneringsmetodeKode )
SELECT DelledningsID, SaneringsmetodeKode
FROM SaneringsprojektImport WHERE DelledningsID NOT IN
(SELECT DelledningsID FROM NySaneringsData)

HTH

Jonathan

edited Aug 08 '16 at 09:48

answered Aug 08 '16 at 07:10

score 1 · Answer 4 · answered Aug 08 '16 at 08:14

Try this:

if (Select ID from table1 Where Code = 'Something') IS NULL
INSERT INTO table1(ID, Code, Description)
Values (.., 'SomeCode', 'Some Description')

OR

if (Select ID from table1 Where Code = 'Something') IS NULL
INSERT INTO table1(ID, Code, Description)
Select ID, Code, Description form table2 
where SomeCondition

Append query should only append if value not present

4 Answers4