3

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:\temp\test\testTempTable.txt" -c -t"\t" -T -S  "testSQlServer" -E -e c:\temp\test\error.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

enter image description here

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ProgSky
  • 2,530
  • 8
  • 39
  • 65
  • when you insert rows without the Identity column, what is the value assigned to it? Are you sure it is not an auto increment column? In which case, you do not need to explicitly specify what that value should be.. the DB will do it for you – Harry Sep 17 '18 at 20:48
  • I have added the table and how its value looks when there is no identity column. Also, it is a auto increment column but BCP is unable to detect or I am doing something wrong. Later might be true :) – ProgSky Sep 17 '18 at 20:53
  • 1
    Use -f parameter to specify a format file. Format file will do a mapping from your csv to your table. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/format-files-for-importing-or-exporting-data-sql-server?view=sql-server-2017 – DanB Sep 17 '18 at 20:57
  • 1
    Alternatively, import to a temp table that doesn't have ID column, and then run a query that copy data from temp table to test_temptable table. – DanB Sep 17 '18 at 20:57
  • Thanks all. But why is BCP unable to populate [ID] column when I run bcp command. Should it not insert rows with incremented ID column ? – ProgSky Sep 17 '18 at 20:59

2 Answers2

3

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!

ammills01
  • 709
  • 6
  • 28
1

You can create file format in XML and skip the identity column. But the identity column must be the last one.

From Microsoft site:
With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. However, you can import into all but the last column of a table. If you have to skip any column other than the last column, you must create a view of the target table that contains only the columns contained in the data file.

Šimon
  • 11
  • 2