SQL Server Bulk Insert Skip Primary Key Violations
<p>I'm currently using SQL Server's Native <a href="https://msdn.microsoft.com/en-us/library/ms188365.aspx?f=255&amp;MSPPError=-2147217396" rel="nofollow noreferrer">Bulk Insert</a> procedure to load several tables into a database from text files. </p>

<p>This has been working well and even handles truncation,null,and formatting errors gracefully. If any column is too large, is null when it shouldn't be, or if there is an incorrect number of columns found based on the format file, those rows are skipped and forwarded to an error file. </p>

<p>Recently however, a duplicate primary key was entered into one of my text files. This caused the bulk insert to simply fail, no row was forwarded to the error file.</p>

<p>Currently my bulk insert commands look like this:</p>

<pre><code>BULK INSERT table.table FROM 'data_file.txt' WITH(
    BATCHSIZE = 100000,
    CODEPAGE = 1252,
    DATAFILETYPE = 'char',
    FIRSTROW = 2,
    KEEPNULLS,
    MAXERRORS = 1000000000,
    TABLOCK,
    FORMATFILE = 'format_file.txt',
    ERRORFILE = 'error_file.txt'
)
</code></pre>

<p>Is there a way, using bulk insert, to handle primary key violations the same way other errors are handled? Ideally I would like rows containing PK violations forwarded to the same error file. </p>