Solved

Append only non existing records

Posted on 2014-03-18
14
6,973 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

929 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

14 Experts available now in Live!

Get 1:1 Help Now