How can I bcp a table out, ignoring one or more columns?


I'm in the process of verifying the contents of replicated tables in ASE12.5. Sometimes, the source and the target table will have identical rowcounts, and the bcp out fles will have identical filesizes. It is still possible however that a source table will contain a 'Y' in a certain field, amd the target table a 'N' in the same field. In that case, both rowcount and bcp out filesize will be identical, but there will still be a difference in the tables.

We have about 2100 replicated tables, the largest one being over 20M rows. I considered several alternatives for checking the consistency, and decided the bcp out file comparison was the easiest, and fastest. Comparing the tables in TSQL is really unpractical, bordering on impossible, especially since the source and target tables reside on different servers. Proxy database could help, but it would be ever so slow.

So I decided to compare bcp out filsizes, and add an md5 check for good measure. I then hit upon the timestamp field, which is not replicated, and therefore varies. Hence different checksums, and no certainty that the tables are identical.

The only other alternative is manually resyncing all the tables, but that;s even more work.

So, can I bcp out a table, omitting the timestamp field?

Many thanks for any suggestions

Johan Genbrugge
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Joe WoodhousePrincipal ConsultantCommented:
You cannot directly exclude columns when bcping out of a *table*.

You can however create a view which is a SELECT of the columns you want, and then bcp out of the *view*.

Have you looked into the rs_subcmp tool that ships as part of RepServer? With proper definitions and properly indexed tables its performance isn't... too awful. I was told a while back that one of the RepServer futures involved a version of rs_subcmp that internally used md5 and it had been benchmarked as being a lot faster. You might be able to use a 15.x rs_subcmp even with the older versions of ASE and RS.

However that's a bit of work to set up. For what you need, views and bcp from the views. You could even script that - build the view programatically to exclude timestamp columns...

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
JojodyneAuthor Commented:
I feel a bit stupid for not thinking of the views! that would of cource work, and can easily be scripted. I'll also look into the rs_subcmp SP which I didn't know existed. I might work very well for the smaller tables! Great answer! Many thanks
Joe WoodhousePrincipal ConsultantCommented:
rs_subcmp is actually an O/S tool, not a stored procedure, you'll find it under the REP% directory. Definitely read up on it first!! ::

Good luck!
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
Sybase Database

From novice to tech pro — start learning today.