insert data into a table

Posted on 2009-02-09
Last Modified: 2013-11-30
Hey gang,
We are on Great Plains version 8. Someone (me) mistakenly deleted 3 years of history of orders from the order headers table. these are linked ot the order details table.

So I am trying to insert this data from a backup into the production system table through DTS.
The error I am receiving is: Primary Key Violation, cannot insert duplicate key into SOP30200 with primary key of "Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Cannot insert duplicate key row in object 'dbo.SOP30200' with unique index 'AK2SOP30200'.".
 (SQL Server Import and Export Wizard)"

I have looked for the duplicate order but cannot find it.
The primary key is on the Order number.

Any help out there???

Question by:carlsteers
    LVL 5

    Expert Comment

    My Suggestion will be , first bring these values into a table from backup using the dts . Make sure this table dosent have any Primary key , you may use identity column .
    Once these tables are populated then you can check and insert the record which is missing .
    LVL 57

    Accepted Solution

    Ok.. I hope AK2SOP30200 is an unique index / Identity column available in that particular table that prevents records inserting into your table

    If that is an identity column, then in order to restore those history records, do the steps below:

    1. Disable the Identity key in that particular table.

    set identity_insert tablename on

    2. Insert records into that table now and you wont face the duplicate records error.
    3. Enable the identity again by issuing

    set identity_insert tablename off

    If that is an unique index, then try the below steps

    1. Drop that Unique index

    DROP INDEX AK2SOP30200  ON table_name
    2. Insert records into that table
    3. Create that Unique index again.

    create unique index AK2SOP30200  ON table_name (col_name)

    Author Comment


    I will try this one on the test system tonight.  

    Author Comment

    OK,  That did not work but it was because the Index row on the data was messed up and 2636 rows out of 82,000 were set at "0". So I have restored all the data and am now inserting it into the table.

    rrregan17 is right.  Thanks!!!
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>rrregan17 is right.  Thanks!!!<<
    Excellent!  This is where you get to accept the solution.  See here if you need to know how to do that:
    How do I close a question?
    LVL 57

    Expert Comment

    by:Raja Jegan R
        This is not totally fair..
    I provided the solution and some others getting rewards for it..
    LVL 57

    Expert Comment

    by:Raja Jegan R
       As my solution helped you out, you should accept my comment as the solution and grant points to me only. But why are you accepting acperkins comment as a solution since he suggested you how to close this question only right..

    You should accept a comment as solution which really helped you to solve your problem..

    Hence I am going to reopen it again..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now