Explicit value must be specified for identity column in table
<p>In sql query editor I have the following commands:</p>

<pre><code>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
</code></pre>

<p>content of the test.txt:</p>

<pre><code>"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
</code></pre>

<p>But when I execute these command I see following result:</p>

<pre><code>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.
</code></pre>

<p>and only 1 row inserts (<code>7861,1194,"B545,B441",12</code>)</p>

<p>table create script:</p>

<pre><code>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 
....
</code></pre>

<p>How to insert using bulk Insert properly?</p>