TSQL BULK INSERT with auto incremented key from .txt file

Question

This is to insert into an already created table:

 CREATE TABLE SERIES(

SERIES_NAME VARCHAR(225) NOT NULL UNIQUE, --MADE VARCHAR(225) & UNIQUE FOR FK REFERENCE
ONGOING_SERIES BIT, --BOOL FOR T/F IF SERIES IS COMPLETED OR NOT
RUN_START DATE, 
RUN_END DATE,
MAIN_CHARACTER VARCHAR(20),
PUBLISHER VARCHAR(12),
S_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
CONSTRAINT chk_DATES CHECK (RUN_START < RUN_END)
)

and the text file is organized as:

GREEN LANTERN,0,2005-07-01,2011-09-01,HAL JORDAN,DC

SPIDERMAN,0,2005-07-01,2011-09-01,PETER PARKER,MARVEL

I have already tried adding commas to the end of each line in .txt file I have also tried adding ,' ' to the end of each line. Any suggestions?

Possible duplicate of [BULK INSERT with identity (auto-increment) column](http://stackoverflow.com/questions/10851065/bulk-insert-with-identity-auto-increment-column) — fqhv, Apr 21 '17 at 20:16

score 0 · Answer 1 · answered Apr 21 '17 at 20:12

Remove KEEPIDENTIY from your BULK INSERT, since that specifies that you want to use the values in the source text file as your IDENTITY.

If this still fails, try adding a VIEW on the table that excludes the IDENTITY field, and INSERT into that instead, e.g.:

CREATE VIEW SeriesBulkInsertTarget 
AS
    SELECT Series_Name,
        Ongoing_Series,
        Run_Start,
        Run_End,
        Main_Character,
        Publisher
    FROM SERIES

score 0 · Accepted Answer · answered Apr 23 '17 at 08:32

Indeed, the KEEPIDENTITY prevents the bulk insert from taken place. Removing the statement however won't resolve the problem.

Msg 4864, Level 16, State 1, Line 13
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 7 (S_ID).

The bulk insert expects to update all the columns. Another way of solving this issue is adding a format file for the text file, see MS Docs - Use a Format File to Bulk Import Data

You can create a format file for your text file with the following command.

bcp yourdatabase.dbo.series format nul -c -f D:\test.fmt -t, -T

Remove the last row, update the number of columns, and replace the last comma with the row terminator. The result will look like as shown below.

13.0
6
1       SQLCHAR             0       255     ","      1     SERIES_NAME                      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       1       ","      2     ONGOING_SERIES                   ""
3       SQLCHAR             0       11      ","      3     RUN_START                        ""
4       SQLCHAR             0       11      ","      4     RUN_END                          ""
5       SQLCHAR             0       510     ","      5     MAIN_CHARACTER                   SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             0       510     "\r\n"   6     PUBLISHER                        SQL_Latin1_General_CP1_CI_AS

TSQL BULK INSERT with auto incremented key from .txt file

2 Answers2