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

Posted on 2008-11-08
Last Modified: 2012-05-05

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
Question by:Jojodyne
    LVL 24

    Accepted Solution

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

    Author Closing Comment

    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
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    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!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    To form a query 10 377
    Help with Solaris 2.4 Importing of a Database 4 121
    Need help with SYBASE syntax 5 40
    SQL Query Syntax 12 54
    Disabling the Directory Sync Service Account in Office 365 will stop directory synchronization from working.
    Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now