Solved

Append only non existing records

Posted on 2014-03-18
14
6,647 Views
1 Endorsement
Last Modified: 2014-03-19
Good morning Experts,
I have a question regarding the proper method of appending data to a table without adding duplicates...

For example, with two tables:
TABLE1:
Field1 (Key field, no duplicates), Field2 (data)
Target table to append to.

TABLE2:
Field1 (Key field, no duplicates), Field2 (data)
Source Table with rows being added.

I want to append any newly added rows from TABLE2 to TABLE1  (items where the Key Field1 does not appear in TABLE1 but does in TABLE2).
I currently APPEND all data from TABLE2 to TABLE1 and get a message saying "cannot append all records due to key field violations, continue with query?"... clicking on yes gets the job done.
But I do not think that this is the right way to do this as surely there is a way without the key field violations.

So... how do I write an Append query to transfer only the missing data from one table to the other without the query trying to append those records already in the target table?

Thanks in advance,
Steve.
1
Comment
Question by:Steve
  • 5
  • 2
  • 2
  • +4
14 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39936392
try this

INSERT INTO TABLE2 (Field1,   Field2 )  
SELECT Field1,   Field2 from TABLE1
where Field1 not in ( Select Field1 from TABLE2)
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39936398
Something like...

insert into Table1 (Key, Field1...)
  select Key, Field1...
  from Table2 T2
  where not exists (select 1 from Table1 T1 where T1.Key = T2.Key)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39936402
In general SQL (Don't know if Access would execute it this way)

INSERT INTO TABLE1
SELECT * FROM TABLE2
WHERE NOT EXISTS (SELECT * FROM TABLE1 WHERE TABLE1.Field1 = TABLE2.Field1)

Bye, Olaf.
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39936407
Isn't an APPEND query an UPDATE?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 300 total points
ID: 39936966
I would do it with a left join rather than a sub query.  Depending on what BE database you are using, performance may be better with a left join.  Not all sub queries can be represented as joins but many can.

insert into Table1 (Key, Field1...)
  select Key, Field1...
  from  Table1 Left Join Table2 On Table1.Key = Table2.Key
  Where Table2.Key Is Null;

Isn't an APPEND query an UPDATE?
 Although an append query updates a table, it is different from an update query.  An append query adds new rows whereas an update query updates existing rows.
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 200 total points
ID: 39936997
The left join might work faster, yes, but it should then be:

Insert into Table1
  select Tabel2.*
  from  Table2 Left Join Table1 On Table1.Field1 = Table2.Field1
  Where Table1.Field1 Is Null;

Open in new window


This works, because if the left join does not find a match in Table1, in regard of the query result Table1.Field1 will be NULL, even though there is no NULL in any record of Table1. That's the nature of an outer (left or right) join, all records of one table are in the result, with or without matches fulfilling the join condition. To only join the nonmatches you check for NULL in any of the joined fields, as that'll be the default value for a missing record even in non nullable fields of the underlying table. What's nulled is the fields in the query result on the table1 part of the row, and that means that record is missing in table1 and therefore you want to append it.

Bye, Olaf.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39937111
Thanks Olaf.  That's what happens sometimes with air code.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39937624
No problem, it was a good idea and advice anyway, Pat.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938567
So there you have it three methods:
NOT IN
NOT EXISTS
LEFT JOIN

You could probably add the solution using EXCEPT.
0
 
LVL 24

Author Closing Comment

by:Steve
ID: 39939049
Thank you very much to all.

The use of left join from Pat and Olaf was better than using a Sub Query so this is the solution which worked best for me.
Pats initial comment was most of the way there, with Olaf providing the rest.

Thanks again.
Steve.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39939580
Thanks, Steve.

EXCEPT is also interesting, but not part of the Access SQL Dialect. From what I found you're typically pointed to NOT EXIST subqueries to replace EXCEPT.

If Table1 should also be updated with changed Table2 data, MERGE would be be the 5th option, but Access also doesn't know MERGE.

Bye, Olaf.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39940258
EXCEPT is also interesting, but not part of the Access SQL Dialect
Then perhaps they should not have posted the question in the MS SQL Server topic area.  :)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39940871
That's fair enough, but "Append Query" is a typical Access term, and if a question is around SQL, why not add MSSQL? Surely many MSSQL experts know Access, too.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39941527
Cripes no... I forgot my Access days as fast as possible as soon as SQL Server Express was released.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now