Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: About bcp out/in

    Hi All,

    I have 20 table in my database in need to take out the data present in that 20 tables i can do this by useing bcp out. but my thing is that i want that 20 tables data at time i.e no need exqute of bcp out each and every time.
    is there any way and i want to do bcp in all the data in same manar.

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Execute this query in the database in which the tables you want to bcp-out are in:
    select "bcp dbname.." + name + " out " + name + ".bcp -c -t; -r\n -Sservername -Uuser -Ppassword" + char(10)
    from sysobjects
    where type like "%U%"

    It produces a bcp-out-script for all of the tables. Put the output in a shelscript or windows-cmd-file and execute it.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Feb 2008
    Posts
    27
    Hi Martijnvs,

    if i execute the above query in which path it is going to store.And what is the column name.Can u tel me some cleary i am not getting compltly.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    In the systemtable sysobjects are stored various things about objects in a database, i.e. tables, stored procedures, etcetera. With the query I mentioned, you select the column that stores the name of objects. Withe the where-clause you limit the query so it only selects tables you created, not systemtables or procedures. The text between quotes contains text that is put in front or after the selected columns. You can execute the query on any database, it won't do anything but select data.

    If you execute a bcp-command by hand, it would look like this:
    bcp master..tablename out <path>\tablename.bcp -c -t; -r\n -Smyserver -Uloginname -Ppassword
    The query I mentioned generates this command for all usertables in a database. You can execute the output of the query from the commandline.
    The outputfiles are placed in the location you specify with <path> or, if you only specify the filename, in the folder you execute the command in.
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Feb 2008
    Posts
    27
    Thaks a lot Martijnvs

  6. #6
    Join Date
    Feb 2008
    Posts
    27
    Hi,


    Execute this query in the database in which the tables you want to bcp-out are in:
    select "bcp dbname.." + name + " out " + name + ".bcp -c -t; -r\n -Sservername -Uuser -Ppassword" + char(10)
    from sysobjects
    where type like "%U%"

    It produces a bcp-out-script for all of the tables. Put the output in a shelscript or windows-cmd-file and execute it.

    what is that char(10) what it does do?

    will it accept only char.....,

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    char(10) means a new line/carriage return is inserted. It's not neccesary in this case. You can leave the '+ char(10)' part out.
    I'm not crazy, I'm an aeroplane!

  8. #8
    Join Date
    Feb 2008
    Posts
    27
    Hi Martijnvs,

    select "bcp dbname.." + name + " out " + name + ".bcp -c -t; -r\n -Sservername -Uuser -Ppassword" + char(10)
    from sysobjects
    where type like "%U%"

    wiil this bcp command will work in sybase level, normally all bcp commands will excute in OS level..........,

  9. #9
    Join Date
    Jul 2004
    Posts
    14
    Dear Sridhar,

    select "bcp dbname.." + name + " out " + name + ".bcp -c -t; -r\n -Sservername -Uuser -Ppassword" + char(10) from sysobjects
    where type='U" and like "%TBPURCHASE%" ( like is for matching the 20table names , you may have more tables in your DB)
    has to be run in sybase command mode, ie at isql prompt or winisql promt or any other sybase client prompt. The output can be put in example.BAT file for windows, and for unix systems put the output into example.sh and run the file
    example.bat at windows prompt or sh exampl.sh at unix prompt

    Ensure your giving the execute permission for unix file (chmod 700 example.sh)

    hope this helps you.

    Umakant P Mirji

  10. #10
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Quote Originally Posted by umakantmirji
    Dear Sridhar,

    select "bcp dbname.." + name + " out " + name + ".bcp -c -t; -r\n -Sservername -Uuser -Ppassword" + char(10) from sysobjects
    where type='U" and like "%TBPURCHASE%" ( like is for matching the 20table names , you may have more tables in your DB)
    has to be run in sybase command mode, ie at isql prompt or winisql promt or any other sybase client prompt. The output can be put in example.BAT file for windows, and for unix systems put the output into example.sh and run the file
    example.bat at windows prompt or sh exampl.sh at unix prompt

    Ensure your giving the execute permission for unix file (chmod 700 example.sh)

    hope this helps you.

    Umakant P Mirji
    Exactly. A short version of this explanation was also in my very first post.
    Good luck.
    I'm not crazy, I'm an aeroplane!

  11. #11
    Join Date
    Feb 2008
    Posts
    27
    than's a lot for all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •