[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to make sure the existing record is not inserted into the table

Posted on 2014-09-16
4
Medium Priority
?
171 Views
Last Modified: 2014-09-30
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PKC_ET_Ut_Base_EmailAddress'. Cannot insert duplicate key in object 'dbo.ET_Ust_Base'. The duplicate key value is (mi05@aol.com).".

Hi experts, since I have a primary key I cannot insert the duplicate value, is there a way I can only insert records that are not in the table. I am using SSIS to load the contents of CSV file into a table, please suggest.
Thanks!
0
Comment
Question by:sqlcurious
4 Comments
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 40325151
You can use the LOOKUP component from SSIS. What it does is to check the existence of a row and then you can define the actions for when it's true or false.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1000 total points
ID: 40325163
If I read the question correctly (which I might not be doing, as you have given few specifics apart from the error message), you have a Flat File Source and an OLE DB Destination, and some of the export into the OLE DB fails, and you want it not to fail.

Then go into the Error Output tab of the OLE DB Destination Editor and change "Error: Fail component" to "Error: Ignore failure", or "Error: Redirect row" if you want errors to be documented.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326929
i would handle it differently:  Never attempt to add data directly into Production.  Instead add the data to a staging table.  you can then validate as much as you need and then and only then import in your case non-duplicate data into Production using simple T-SQL techniques (GROUP BY, DISTINCT, CTEs, etc.).
0
 

Author Closing Comment

by:sqlcurious
ID: 40353131
thanks
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses

640 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