pull data from sybase

Posted on 2009-04-29
Last Modified: 2012-05-06
I have a query in sybase (sql)  and i want to pull the data from that sql to a flat file using a shell script.

Just as we use sqlplus to pull oracle data and spool it to a file.

How to do this?
Question by:vishali_vishu
    LVL 14

    Expert Comment

    You may use isql or bcp.

    isql lets you run any query or procedure, but output will contain some clutter (names of columns, horizontal dividing lines, number of returned records etc)

    bcp cen give you clear data, but you can output only tables or views (so you have to store your query as a view and then you can use bcp).

    See for detailed explanation of both these utilities.
    LVL 40

    Expert Comment

    I'm not as familiar with Sybase as with Oracle, but I do use dbisql just a bit, here is what I know

    I have a script tables.sql to list the tables in my Sybase database

    select * from sysobjects where type = 'U'

    dbisql -c "UID=dba;PWD=sql" tables.sql > results.txt

    Open in new window

    LVL 1

    Author Comment

    is it possible to pull to pull few columns from a table (sybase table).

    ex: Table_x (col_1,col_2,col_3,col_4,col_5).

    can i use the bcp to pull only col_2 and col_5 ?

    LVL 13

    Accepted Solution

    Bcp is a very flexible tool. You can use bcp with a format file and specify what columns to be included, format of them and delimiters. Basic syntax of bcp:

    bcp [[database_name.]owner.]table_name[:slice_number] {in | out} datafile
            [-m maxerrors] [-f formatfile] [-e errfile]
            [-F firstrow] [-L lastrow] [-b batchsize]
            [-n] [-c] [-t field_terminator] [-r row_terminator]
            [-U username] [-P password] [-I interfaces_file] [-S server]

    You can use bcp with "out" option and without -f -c or -n options to create a sample format file. Bcp asks you the datatype, data length etc. for each column. Then you can save the format file at the end. After then you can edit format file by removing undesired columns. Be careful that it inlcudes number of columns at the beginning. You need to change it as well.

    Or you can create a format file by checking Sybase manuals.
    LVL 13

    Expert Comment

    By the way, if you want a quicker solution without more flexilbility, creating a view including only these columns would be easier.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Read about achieving the basic levels of HRIS security in the workplace.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now