Solved

Append only non existing records

Posted on 2014-03-18
14
8,208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 12

Expert Comment

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

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.
1
 
LVL 36

Expert Comment

by:PatHartman
ID: 39937111
Thanks Olaf.  That's what happens sometimes with air code.
0
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

749 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