Link to home
Start Free TrialLog in
Avatar of danidak
danidakFlag for United States of America

asked on

-- Need to improve the current bcp solution --

Hi,
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.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Jan Franek
Jan Franek
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mansoor_a_khan
mansoor_a_khan

I would suggest create a unique index on the temp table and then BCP the data in, and if you are about to insert dup keys in the table u will get the dup error and u can trace with the no rows inserted count to track whe the dup key is.

However this solution combined with DIFF will to the trick for u.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would also recommend the CIS approach if the servers are on the same network.

-bret