-- Need to improve the current bcp solution --
Posted on 2004-08-17
Currently I'm copying data from multiple tables on one server to a different server using bcp, which works fine. However because I have to make sure that each record that is copied it's not a duplicate that already exists on my second server and if I find one I write it to a file so I know what duplicates I got. Anyhow, because bcp does not support duplicate record error checking which would give me the duplicate record itself, in order to do this using bcp I have to check each record one at the time for any error messages like "bcp copy in failed" which would indicate to me that I have an dup. record. (using ksh).
Everything works fine, but the problem is speed, often I have to copy 50,000 records every day and it takes about 3 hours to do that using my current method. I need something that will do the job in less than an hour.
I can not use bulk insert because it is disabled on the servers and I don't have the rights to change it.
Basically what I need is to copy records from one server to another, and any duplicate records should be written to a file. Any suggestions are welcomed.