Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2444
  • Last Modified:

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
0
nivo_Z
Asked:
nivo_Z
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now