Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

BCP Large Table ( But not all Data)

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

0
Peewee
Asked:
Peewee
  • 5
  • 3
1 Solution
 
Joe WoodhousePrincipal ConsultantCommented:
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!
0
 
PeeweeAuthor Commented:
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?
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
PeeweeAuthor Commented:
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

0
 
Joe WoodhousePrincipal ConsultantCommented:
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.)
0
 
PeeweeAuthor Commented:
Hi Joe,
do u have example code on how to create a proxy table.

thx pg
0
 
Joe WoodhousePrincipal ConsultantCommented:
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!
0
 
Joe WoodhousePrincipal ConsultantCommented:
Glad we could help!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now