Append data to a table using BCP utility

HI,

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?

Thanks,
Nivo
nivo_ZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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...

e.g.

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

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


hth
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptjcbCommented:
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.

0
LowfatspreadCommented:
you can use BCP to append to an existing table...

you just have to know before hand  that the data is Unique
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.