bcp import file how to ignore duplicates?

Question

I want to import a csv file into a sql server 2008 R2 database table using bcp using a command like this:

bcp Database..TableName in TableName.csv -n -T -E -S localhost

My problem: the table contains one row which is also in the csv file (default row inserted on creation of the database).

Can I somehow instruct bcp to ignore this line? I tried -m for max error count, but bcp still aborts when it hits the already existing record (primary key).

I can't just delete this record, since it contains installation-dependend information I'll need later on.

score 2 · Accepted Answer · answered Sep 27 '10 at 21:04

bcp does not have a lot of sophisticated error handling for situations like this.

I think your alternatives are:

Remove the one duplicate row from your csv.

Write a DTS/SSIS package that will allow for more sophisticated error handling.

bcp into a temporary table and use t-sql to insert from the temporary to the live, using a left join to check for existence of the row. Something like:

insert into TableName
    (column1, column2, ...)
    select t1.column1, t1.column2, ...
        from TempTableName t1
            left join TableName t2
                on t1.primarykey = t2.primarykey
        where t2.primarykey is null /* row does not already exist */

bcp import file how to ignore duplicates?

1 Answers1