insert data into a table

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???

Who is Participating?
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
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)
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 .
carlsteersAuthor Commented:

I will try this one on the test system tonight.  
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

carlsteersAuthor Commented:
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!!!
Anthony PerkinsCommented:
>>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?
Raja Jegan RSQL Server DBA & ArchitectCommented:
    This is not totally fair..
I provided the solution and some others getting rewards for it..
Raja Jegan RSQL Server DBA & ArchitectCommented:
   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..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.