Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 */
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.