We help IT Professionals succeed at work.

insert data into a table

Medium Priority
461 Views
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???

Comment
Watch Question

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 .
 
Thanks
Ram
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
rrjegan17,

I will try this one on the test system tonight.  
Thanks!

Author

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!!!
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>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?
http://www.experts-exchange.com/help.jsp
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
carlsteers,
    This is not totally fair..
I provided the solution and some others getting rewards for it..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
carlsteers,
   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..
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.