SQL BCP Unable to insert value for identity column

I am using bcp utility to import a tab delimited file. My table has an identity column, when I run bcp command I get error as

Error = [Microsoft][SQL Server Native Client 11.0] Invalid date format

Second column is datetime. When i use -E flag (as suggested by many), I get error as

Error = [Microsoft][SQL Server Native Client 11.0] Invalid character value for cast specification

Everything works when I don’t have the identity column. I am not sure what is missing here.

Here is a repro:

CREATE TABLE [dbo].[test_temptable]
(
    [ID] int NOT NULL IDENTITY PRIMARY KEY,
    [Date] [datetime] NULL,
    [Column2] [varchar](100) NULL,
    [Column3] [varchar](100) NULL,
) 
ON [PRIMARY]
GO

Test Data (testTempTable.txt – TAB DELIMITED):

15-Sep-18   TestColumn2 TestColumn3

BCP Command.

bcp "testDB.dbo.test_temptable" in  "c:temptesttestTempTable.txt" -c -t"t" -T -S  "testSQlServer" -E -e c:temptesterror.csv

When I drop the [ID] column from table and run , everything works fine.

What am I doing wrong?

EDIT: Value assigned when table does not have Identity Column

useful links:

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/keep-identity-values-when-bulk-importing-data-sql-server?view=sql-server-2017

What will be BCP format for inserting a identity column

Answer

The issue is that you are trying to add the first column, which is a date, into an INT column.

You have three options…

1. Add an INT column to the source data as the first row and have it incremented like an IDENTITY would be incremented and continue to pass the -E option. Doing this will allow the data from the source to be used as the IDENTITY column.

2. Add a random INT to the first column of your source data, say 1 for every row, then do not pass in the -E. According to the documentation, when -E is not provided it will ignore the values for the identity column and start at the currently seeded value and auto-increment.

3. Leverage a format file to specify which columns from your data file go into which columns in our SQL table.

How to specify the format file

How to construct a format file

Good luck!