CODEWITHSUNDEEP
sqlsql-serverbulkinsertbcp
Ravee
Ravee

Reputation: 41

SQL server bulk insert identity column

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

Upvotes: 1

Views: 2290

Answers (1)

Sonam
Sonam

Reputation: 3466

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.

Upvotes: 2

Related Questions