Databases
StackOverflow2013
Postsdbo
POPrimary key violation on trivial BCP insert
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPrimary key violation on trivial BCP insert
    text
    copied!<p>I'm trying out the BCP utility on SQL Server 2008 Express. I don't think that what I'm trying to do could be more trivial, but still I'm getting a primary key violation when trying to insert two rows into an empty table.</p> <p>Here is the table DDL:</p> <pre><code>CREATE TABLE [dbo].[BOOKS]( [BOOK_ID] [numeric](18, 0) NOT NULL, [BOOK_DESCRIPTION] [varchar](200) NULL, CONSTRAINT [BOOKS PK] PRIMARY KEY CLUSTERED ( [BOOK_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO </code></pre> <p>Here is the BCP format file:</p> <pre><code>10.0 2 1 SQLNUMERIC 0 3 "\t" 1 BOOK_ID "" 2 SQLCHAR 0 0 "\r\n" 2 BOOK_DESCRIPTION Modern_Spanish_CI_AS </code></pre> <p>and here is my input file:</p> <pre><code>101 BOOK_ABC_001&lt;CR&gt;&lt;LF&gt; 102 BOOK_ABC_002&lt;CR&gt;&lt;LF&gt; </code></pre> <p>finally here is the command I run:</p> <pre><code>bcp Database.dbo.BOOKS in books.txt -T -f BOOKS-format.fmt </code></pre> <p>and here is the error I get:</p> <pre><code>Starting copy... SQLState = 23000, NativeError = 2627 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of PRIMARY KEY constraint 'BOOKS PK'. Cannot insert duplicate key in object 'dbo.BOOKS'. SQLState = 01000, NativeError = 3621 Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated. BCP copy in failed </code></pre> <p>Now, BCP succeeds if I use an input file with a single line. In this case, the BOOK_ID column gets assigned a value of 0. So it seems that the first field in my input file is being ignored, and 0 is being used as the value for BOOK_ID for all the rows, which would explain the PK violation error.</p> <p>So the question is, what is wrong in my format or input files that causes the first column to be ignored?</p> <p>Thanks.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload