Append data to a table using BCP utility

Posted on 2006-04-03
Last Modified: 2012-06-27

I'm a developer with a little DB knowledge. I need to develop a code that among other things, imports and exports a table to/from file.
I'm using the bcp utility, and it all goes OK.
I need to implement both append and truncate. Meaning, if the user chooses to import data from file he can ask that previous data will be deleted, and the table will contain only the data in the file. This goes OK also, I first truncate the table and then use the BCP utility.

My problem is with the append. The first time, the BCP tries to upload a record that its key already exists, it stops. I want the BCP to skip records that already exist.
I've tried to use -m parameter but it didn't help and I didn't find any other parameter that fits.

I'm using MSSQL 8.00.194. The parameters that I use for exporting the table to file are:
bcp table_name file_name -e -c -C -U -P -S -t -r
Any ideas?

Question by:nivo_Z
    LVL 50

    Accepted Solution

    BCP does not provide a mechanism to UPDATE existing rows... if a row to be loaded exists then that is an error...

    probably the way to handle this is to load into a staging table with the same format as the destination table to start with...
    then once all the data is available do an appropriate insert into the destination table...


    insert into dest  (col list)
      select * from stage
        where not exists (select pk from dest where =

    or do an update of any existing rows and then follow it with the insert of new records....

    LVL 27

    Expert Comment

    Lowfatspread is right - you do not use bcp to append. Using the -m  is sort of like trying to add a "ON ERROR RESUME NEXT" feature to bcp that will not work.

    SQL does not allow any easy way to append from a file - bcp, bulk insert, and XML Bulk Load all expect to load data into empty tables. You would have to write custom procedures to add to what is already there. You could check out writing queries with SELECT OPENROWSET - although Lowfatspread's suggestion of staging tables with a second query to move the data into your existing table should be the easiest to do.

    LVL 50

    Expert Comment

    you can use BCP to append to an existing table...

    you just have to know before hand  that the data is Unique

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now