Importing Data in SQL Server 2000. “Integrity violation error” in RowID column
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
April 5, 2005 7:18 PM
UPDATED:
April 8, 2005 11:12 AM
Answer Wiki:
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!
To see all answers submitted to the Answer Wiki: View Answer History.
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’.
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