BCP Large Table ( But not all Data)

Posted on 2005-02-28
Medium Priority
Last Modified: 2012-06-27
Hi There,

is there a way that you can bcp down only selected data from the target table. ie  I have a table with 3 million rows, and i'm only interested in 500,000 of the rows.  The rows i'm interested in have a column named type with a value of 'AA'.

thanks in advance peewee

Question by:Peewee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13418726
So long as you're using bcp 11.1 or above, you can create a VIEW which contains WHERE clauses to restrict the result set to the rows you want, and then bcp out of the view.

In your case it would be something like

CREATE VIEW bcp_view
from       [table]
WHERE   type="AA"

and then bcp as usual:

bcp [database].[owner].bcp_view out [file] [etc]

Note - if you don't have an index whose leading column is on the "type" column, this will be slow as the view will cause a table scan.

Good luck!

Author Comment

ID: 13427815
Hi Joe,
thanks for your comments, there is one problem, i only have read only access to the database. I am unable to create views on the db.

Do i have another option?
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13428240
If you had a reliable row order (ie, a clustered index and "allpages" locking on this table), you could specify a "first row" and "last row" as part of the bcp command line ("-F" and "-L" respectively).

Is the problem that you're readonly in this database, or at all anywhere in this Sybase server? If just the former, you could create the view in tempdb, referring to the table in your database. Odds are good you can create objects in tempdb. You could then bcp out of tempdb..[view]. The view would be lost the next time Sybase was restarted, but you could always recreate it.

Short of that, I don't know of any other way to do this natively in Sybase. In your situation, assuming I had disk space, I'd probably bcp out the lot and come with some from of parsing at the operating system level. In UNIX/Linux, you could whip up something in awk or perl.
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf


Author Comment

ID: 13431217
Hi Joe,
i have write access on another sybase server on our network, but not on the one which i need to copy the data from.

Can i create a view on the sybase server where i have write access that captures the subset data i need to bcp from the server in which i have only read only access.

If so what would be my sql to create such a view?

regards Peewee

LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13434809
Did you try in tempdb?

I don't think you've said what version of ASE you're using. If it's 11.5 or above, you may have the ability to use "CIS" or "proxy tables" to define a proxy table in your write server that points to the real table in the readonly server. You could then build a view on this and bcp as above.

This is a complicated process and most of the setup steps can only be done by someone with SA privileges, so I'm guessing you won't be able to do this for yourself.

Can a DBA create the view for you? Point out that it isn't actually writing to the database.

Oh, one more idea that's related - what if you created a table in tempdb (not a private #temptable, just a regular table) based on a query from the main table?

INTO      tempdb..bcp_table
FROM     [table]
WHERE   [...]

And then bcp out of the table in tempdb? You almost certainly can create tables in tempdb, too. (A view is better because it doesn't need to actually copy the rows.)

Author Comment

ID: 13438503
Hi Joe,
do u have example code on how to create a proxy table.

thx pg
LVL 24

Accepted Solution

Joe Woodhouse earned 300 total points
ID: 13445254
1) (On both servers) Add an entry for the other server to each server's interfaces files, if one is not already there.

2) (On both servers, needs SA permissions) Add an entry for the other server in each server's master..sysservers table using:

sp_addserver [other server name]

3) (On your "write" server, needs SA permissions) Create a login mapping that tells CIS who to treat you as when you connect via CIS. The easiest way to do this is if your login name and password are identical in both servers. You'd then have to run:

sp_addexternlogin [remote server], [local login], [remote login], [remote password]

4) (On your "write" server, needs create table permissions) Create the proxy table:

create proxy_table [local proxy table name]
at "[remote server].[remote database].[remote owner].[remote table]"

That syntax works in ASE 11.9.2 and above. If you're running 11.5, you need different syntax. If you're at 11.0 or lower, you can't do this.

Note that the preparation requires SA permissions on your "readonly" server - and if you have those, you can just create a view locally. 8->

Good luck!
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13454065
Glad we could help!

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn how to use the free Acronis True Image app to easily transfer data between iPhones and Android phones.
This month, Experts Exchange’s free Course of the Month is focused on CompTIA IT Fundamentals.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

801 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