Importing Data in SQL Server 2000. “Integrity violation error” in RowID column

pts.
Tags:
Database Management Systems
MySQL
Oracle
Hi All, I have a table called Employees(RowID,EmpNo, Lastname, Firstname). Here RowID is autogenerated by the system. It is the primary key and DOES NOT allow NULL values. I CANNOT change the table structure. I am trying to import data from a text file through the Import Data Wizard in the Enterprise Manager. The textfile has all the above columns(EXCEPT the RowID as it should be autogenerated). At the end of the import, the system gives me the error "Column RowID - Integrity violation attempt to insert NULL values". I have tried to disable/enable the "Enable identity insert option" in the import wizard but it does not work. Can you please tell me what could be the solution for this error?

Answer Wiki

Thanks. We'll let you know when a new response is added.

IIRC, importing data does NOT trigger any auto-populate fields for each record.

You have a few options: try bringing the data in with a DTS package, bulk upload, or modify the source text file and insert the RowID field values by hand.

Or if you want to get really creative, import the text file to Excel, insert a column to represent the RowID data, and have Excel populate the cells. Then DTS the data in from Excel instead of the text file.

Good luck!

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jhoman
    DTS will load the table with no problem. Just make sure the transformation tab in the 'Transform Data Task' shows that no data being copied into your primary key field. You might also check to make sure that the primary key field is also identified as a 'Table Identity Column'.
    0 pointsBadges:
    report
  • HappyGene
    K, I second the above reccommendations, but what I.ve started doing is DTS.ing or BC.ing the data into a plain vanilla table (*no* triggers, keys, rel.s, etc.) and then querying those rows into my real table. SS will handle the auto fields for you. It is a procedure beyond what we expect to have to use based on the advertised capabilities of DTS, but it *always* works for me. You can even sp this to create the temp, fill it up, and then append/update and drop the temp. Also, if you.re popping a foreign key during this, insert appropriate constraint dropping/adding verbage into the sp. When you test, don.t immediately worry about poor response if users are in the tables because SS has to work the table altering into a series of transactions so that existing rel.s and triggers aren.t bypassed by client requests. Good luck, :) Gene
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following