Hi there,

I want to import data file into SQL Server using the BCP command. Some
of the data records already exist in the database. How can I make the
BCP insert the new data into the database whilst discard the existing
ones?

Further more, how can I make BCP to perform followings.

1) for data not exist in the database, perform insert;
2) for data already exist in database, perform update.

Many thanks.

Jonathan

*** Sent via Developersdex http://www.developersdex.com ***

Insert the data into a staging table and then use normal T-SQL to detect new
vs existing and insert vs update accordingly.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Li Jonathan" <lzb (AT) ongosoft (DOT) com> wrote

You mean to write a program to check that? That would be disaster if we
need to check the records one by one because we need to import over
400,000 records per day.

Is there any parameter I can set for BCP command (or similar command) to
perform that? It seems to me that the BCP would roll back all record on
encountering a single error data item. That is actually not a good
practice because the BCP can produce the error report to tell which
record is wrong and why.

Thanks.

Jonathan


*** Sent via Developersdex http://www.developersdex.com ***

I suggest T-SQL, which I wouldn't exactly class as a program, but either way
a well written query will be set based. BCP does not have a magic switch,
and even if it did, how do you expect it to do this? It has to read both
sets of data, and to my mind using T-SQL and set based language will be the
most efficient.



"Li Jonathan" <lzb (AT) ongosoft (DOT) com> wrote

Hi Darren,

Thanks.

The tool I need is that it performs INSERT for new records and UPDATE
for existing records (or skip over existing records). Yes, I can write a
program to do that but the performance would be 1000% slower than
BCP-level command. And, this is not difficult for BCP developers to have
a option.

Any advice?


*** Sent via Developersdex http://www.developersdex.com ***

"this is not difficult for BCP developers to have a option" - Are you sure?

BCP is all about fast loading of data. There are compromises when fast
loading, such as some checks that in an ideal world would be done are not.
Anything less and it is no longer fast. This is the whole premise of BCP and
the other fast load technologies, so insert vs update requires checks and
therefore is never going to be fast load.

If you think this feature is lacking from BCP then feel free to tell MS,
sqlwish at microsoft.com.



"Li Jonathan" <lzb (AT) ongosoft (DOT) com> wrote