BCP Large Table ( But not all Data)

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
as
SELECT   *
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!

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?

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.

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

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?

SELECT  *
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.)

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

thx pg

Glad we could help!