Solved

DTS error cannot insert duplicate key row

Posted on 2004-11-02
351 Views
Last Modified: 2008-02-01
I've got a DTS package that inserts info from an excel sheet to a table. When I tried to update the table with a new excel sheet I get an error "cannot insert dublicate key row in object "table" with unique index "i_########" . I'm not very good with SQL so any help will be greatly appreciated.
0
Question by:ClintN
    6 Comments
     
    LVL 6

    Expert Comment

    by:Duane Lawrence
    It means it is trying to insert a duplicate row.

    A unique index requires every value in the column to be unique, as in none can be the same.

    Duane
    0
     
    LVL 6

    Accepted Solution

    by:
    I typically import data into a temporary table from any file.

    Then I clean the data to avoid errors like the one you are getting.

    Last I put the data into the core tables of the production database.

    Duane
    0
     
    LVL 6

    Expert Comment

    by:Duane Lawrence

    After you import it into a temporary table run the query below:

    select * from temporaryimporttable where uniquekeycolumnname in ( select uniquekeycolumnname from coretablename)

    Duane
    0
     
    LVL 4

    Expert Comment

    by:eclipse2k
    in the SQL Server Enterprise Manager, right click on the table where you are importing the data to, and choose "All Tasks --> Manage Indexes" or something like that, we use a german Version...

    There you can see all indexes on that table, there should be the index "i_########" as shown in the error message.

    select it, choose "Edit" and uncheck "Unique" or "Unique Values" or something like that (we still use a german Version so dont know the exact wording)
    0
     

    Author Comment

    by:ClintN
    Thanks!! I'm stepping through the processes now. I'll get back to you when I finish.
    0
     

    Author Comment

    by:ClintN
    There were duplicate entries in the excel file. Thanks a million!!!!!!!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    884 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

    18 Experts available now in Live!

    Get 1:1 Help Now