SQL server bulk insert identity column

1
votes

I am trying to bulk insert into the table which has an identity column.But my host file contains empty value for identity column.When I issue 'bcp' command,I get an error -

Starting copy...

SQLState = S1000, NativeError = 0

Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file

SQLState = 23000, NativeError = 515

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert the value NULL into column 'UNIQUE_ID', table 'xx.dbo.yyy'; column does not allow nulls. INSERT fails.

SQLState = 01000, NativeError = 3621

Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement ha s been terminated.

BCP copy in failed

If I dont give empty value for the identity column,then I get an error - unexpected EOF encountered in bcp data-file.

Please help in getting the bcp work to bulk insert into the table..

1
sqlsql-serverbulkinsertbcp
Can you post your statement? Do you have a format file? - Hart CO
I'd add the above to your question, could you show sample data? Having an empty id field in your .txt and not using KEEPIDENTITY should be working. - Hart CO
Yes.My bad..I was using KEEP IDENTITY unknowingly..After removing that,it works.Without specifying the empty id field in data file,is there any option so that I can remove the id field in data file? - Ravee
If you want to remove the empty ID field you'd have to insert into a staging table first, don't think there's an alternative. - Hart CO

1 Answers

2
votes

Although I would suggest you to bulk insert the data into some staging table first and then Insert the data into the main table from staging table. This way you can preserve the identity columns. The next way is to add an additional id column into your host file and keep the column blank and then do the bulk insert. Also please confirm if you are not using the KEEPIDENTITY keyword in the Bulk insert query.