Home / server / Questions / 185050
Accepted
Sam
Asked: 2010-09-28 06:36:53 +0800 CST

bcp import file how to ignore duplicates?

  • 772

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.

sql-server import bcp

1 Answers

  1. Best Answer

    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 */
    • 2