Explicit value must be specified for identity column in table

Question

In sql query editor I have the following commands:

SET IDENTITY_INSERT tUserPreference ON

BULK INSERT tUserPreference
FROM 'D:\test.txt'
WITH
(   KEEPIDENTITY,
    BATCHSIZE = 2,
    FIRSTROW = 2,
    FIELDTERMINATOR = ',' ,
    ROWTERMINATOR = '\n',
    DATAFILETYPE = 'char',
    KEEPNULLS
    );
SET IDENTITY_INSERT tUserPreference OFF
GO

content of the test.txt:

"UserPreferenceId","UserId","UserPreferenceValue","UserPreferenceTypeId"
7858,1194,"FSP,FRB,FWF,FBVS,FRRC",15
7859,1194,"FIRM",21
7860,1194,"009,61,114,142,174,233,262,286,303,325,369,389,411,671,690,735,938,943,977,105,200",11
7861,1194,"B545,B441",12
7862,1194,"{"FIRM":{"accountType":"Firm","timeInForce":"DAY","routing":"DNR","isAllOrNone":false}}",2

But when I execute these command I see following result:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (UserPreferenceTypeId).
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK_tUserPreference_UserPreferenceId'. Cannot insert duplicate key in object 'dbo.tUserPreference'. The duplicate key value is (7859).
The statement has been terminated.

and only 1 row inserts (7861,1194,"B545,B441",12)

table create script:

CREATE TABLE [dbo].[tUserPreference](
    [UserPreferenceId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NULL,
    [UserPreferenceValue] [varchar](max) NULL,
    [UserPreferenceTypeId] [int] NOT NULL,
 CONSTRAINT [PK_tUserPreference_UserPreferenceId] PRIMARY KEY CLUSTERED 
....

How to insert using bulk Insert properly?

You need a format file if you want SQL Server to assign identity values during `BULK INSERT`. [See the documentation](https://msdn.microsoft.com/en-us/library/ms188365.aspx). — Aaron Bertrand, Jun 20 '16 at 14:06
Possible duplicate of [BULK INSERT with identity (auto-increment) column](http://stackoverflow.com/questions/10851065/bulk-insert-with-identity-auto-increment-column) — Chris Pickford, Jun 20 '16 at 14:09
@gstackoverflow See the answers to the linked question; you can either insert to a temporary staging table, then upsert from there, or create a format file. — Chris Pickford, Jun 20 '16 at 14:16
@Chris Pickford I cannot understand how to create format file. I use commas as delimeter and commas inside field — gstackoverflow, Jun 20 '16 at 14:22
If you can't follow the documentation linked by Aaron above or the question linked, then no answer here will help you either! — DavidG, Jun 20 '16 at 14:23
@DavidG should I generate this format file or should I write it manually ? — gstackoverflow, Jun 20 '16 at 14:29
Use the `keepidentity` argument. See https://msdn.microsoft.com/en-us/library/ms188365.aspx. However, you may want to consider a different approach, e.g. bulk inserting into a staging table and then merging the results. — square_particle, Jun 20 '16 at 14:32

Sam · Accepted Answer · 2016-06-20T16:02:39.890

0

CREATE TABLE tUserPreference1 (DataField VARCHAR(1000))

BULK INSERT tUserPreference1
FROM 'C:\Shared\text.txt'
WITH
(   
    FIRSTROW = 2,
    ROWTERMINATOR = '\n',
    DATAFILETYPE = 'char',
    KEEPNULLS
    );

select  SUBSTRING(DataField,1,4)    AS UserPreferenceId
        ,SUBSTRING(DataField,6,4)   AS UserId
        ,REVERSE(SUBSTRING(REVERSE(DataField),1,CHARINDEX(',',REVERSE(DataField))-1))   UserPreferenceTypeId
        ,DataField
INTO    tUserPreference2
from    tUserPreference1

SELECT  *
        ,SUBSTRING(DataField,11,LEN(DataField)-(11+LEN(UserPreferenceTypeId))) AS UserPreferenceValue
FROM    tUserPreference2

edited Jun 20 '16 at 16:02

answered Jun 20 '16 at 14:56

Sam

850
1
10
20

actual file is too long – gstackoverflow Jun 20 '16 at 15:00
Do we at least know the lengths data can have? i.e. isUserPreferenceId always 4 digits? UserId 4 digits? and UserpreferenceTypeId 1 or 2 digits? – Sam Jun 20 '16 at 15:13
Check the edited answer. From this you can get a table with the require data. From there insert it into the table you want. – Sam Jun 20 '16 at 15:52
Invalid column name 'DataField'. – gstackoverflow Jun 20 '16 at 15:54
Edited. Check now. – Sam Jun 20 '16 at 16:02
Msg 207, Level 16, State 1, Line 36 Invalid column name 'DataField'. Msg 207, Level 16, State 1, Line 36 Invalid column name 'DataField'. – gstackoverflow Jun 20 '16 at 16:14
I use sql server 2008 – gstackoverflow Jun 20 '16 at 16:15
SQL Server version doesn't have an issue with the code. Did you copy paste the edited code as is? Can put the code you are running? – Sam Jun 20 '16 at 16:18

Explicit value must be specified for identity column in table

1 Answers1