Append Errors

I have an append query set up in my database that is working well. However, the data that I'm appending often has records that cannot be appended due to key violations. I am wondering if there is a way to set up the append query so that every file that cannot be appended to the original table is instead sent to an error table. If that's not possible, are there any other potential options I can use to find the non-appended files and move them to a table?
rosemary fletcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Best way I've found to accomodate this is to check for the records which do not meet the key violations first, and move them into a temp table, or some other table where you can resolve the key violations if you choose to.  This works best for NULLs where there shouldn't be, but can also be used to identify records that would create duplicates.

Then, once you have either tagged those records, or moved them, then append only the records which won't violate the keys.  This can be cumbersome, and may require half a dozen or more queries to isolate the offending records.
rosemary fletcherAuthor Commented:
I see. That does sound a bit cumbersome. Not to mention that I was hoping to make this an automated process and this appears to be more of a manual thing.
Would setting up a second append query that only finds data that matches what is already in the table being appended to work? Because that sounds a bit simpler.
Dale FyeOwner, Developing Solutions LLCCommented:
I don't know where you are getting your data from, but when I have an append query like this, it is generally because I'm getting data from some external source (CSV or Excel) and the provider has left something out.

I normally automate the upload process, so after selecting the file to be uploaded, my code will run through a series of queries to identify records which will not upload properly (strings in numeric fields, NULLs in fields which don't allow nulls, invalid foreign keys, duplicates in fields which should not have duplicates.  The queries which identify these records, normally dump those records into another table (temp) which includes all of the fields, as well as several other fields (FileName - indicates the file the data came from, Retry (Yes/No) which allows me to retry the upload for those records after I've done something with them (if anything), and Comments - which allows me to identify what was wrong with the original so that I can attempt to fix it.

This code all runs automatically, and then presents me with a form which allows me to examine the descrepancies (if I want), or which can be bypassed until later, to allow the rest of the upload process to work properly.

If the type of key violations you are getting are duplicate records, then you might simply want to modify the append query so that it checks to see whether the values already exist before attempting the append.  You can do this with code that looks similar to:
INSERT INTO Table2 (Field1, Field2, Field3, ...)
SELECT T1.Field1, T1.Field2, T1.Field3, ...
FROM yourTable as T1 LEFT JOIN Table2 as T2 
ON T1.SomeField = T2.SomeField

Open in new window

The SELECT portion of this code is designed to identify unmatched records (Records in "YourTable" which do not already exist in Table 2, base on the value of "SomeField", which would be a field involving a unique key value (probably a PK, but not necessarily).
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

rosemary fletcherAuthor Commented:
The way that this database is designed is that every day, it imports three excel files into three separate tables. Then the append query combines the relevant data from all three tables into a single merged table.  I was thinking that if a second query identical to the merge query was created, but included a criteria to only take data that matched what was already in the merged table, then the errors could be found in a less complicated way. I want the append to occur, just with the errors in a second table so I can make sure that all the imported records are there and that the total matches.
Gustav BrockCIOCommented:
You can also use a Combined Update and Append Query.
Very smart.
rosemary fletcherAuthor Commented:
I'm a bit confused about how a Combined Update and Append Query will help in this situation. From what I can tell, this would be useful if I wanted to both change the data within the merged table and append new data to it. But I don't. I want to append different data from the same 3 original tables to two unique tables.
Gustav BrockCIOCommented:
Then Dale showed you how.
Dale FyeOwner, Developing Solutions LLCCommented:
"Then the append query combines the relevant data from all three tables into a single merged table. "

Is this the process which is causing the key violation errors? or is that the next step in the process, after the 3 are merged into one?

How are you "merging" from the 3 to 1?  Is this a union query, or are you creating a query which joins these three separate tables?  if the latter, have you tried using DISTINCT to ensure that all of the rows you are creating in the "merged" table are unique?
rosemary fletcherAuthor Commented:
Ok, I figured out a plan. All I did was place this: In (SELECT  [MergedTable].[Package Barcode] FROM MergedTable) in the criteria section of a test query and it only selected data with a matching package barcode. All I need to do is run the query before I run the merge and everything should be all set. Thanks for all the advise!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
OP obviously didn't create this solution "on my own" after numerous inputs by both Dale Fye and Gustav Brock
Dale FyeOwner, Developing Solutions LLCCommented:
Just another point.  Us of the IN ( ) predicate in a WHERE clause can be very slow, significantly slower than using an INNER JOIN or a LEFT JOIN as part of a find unmatched type query.
Gustav BrockCIOCommented:
Your method will work, but the optimal solution is that provided by Dale.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.