[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Append only non existing records

Posted on 2014-03-18
14
Medium Priority
?
11,180 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 30

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.
1
Independent Software Vendors: 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!

 
LVL 12

Expert Comment

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

Accepted Solution

by:
PatHartman earned 1200 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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 800 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 39

Expert Comment

by:PatHartman
ID: 39937111
Thanks Olaf.  That's what happens sometimes with air code.
0
 
LVL 30

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 30

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 30

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

650 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