Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Post Unanswered: Using BCP Utility

    Dear Friends,

    Can any of my friends there tell me how to use BCP in/out utility from the grounds up.

    Thank You
    Amit
    A friend in need is a friend in deed

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Code:
    bcp -help
    
    usage: 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]
            [-a display_charset] [-q datafile_charset] [-z language] [-v]
            [-A packet size] [-J client character set]
            [-T text or image size] [-E] [-g id_start_value] [-N] [-X]
            [-M LabelName LabelValue] [-labeled]
            [-K keytab_file] [-R remote_server_principal]
            [-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
    E.g.
    Code:
    bcp master..syslogins out syslogins.bcp -Usa -Pxxx -SServerName -c
    To bcp in, the option "select into/bcp" must be switched to "on"

    Code:
    use master
    go
    sp_dboption MyDB, "select into", true
    go
    use MyDB
    go
    checkpoint
    go

  3. #3
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Cool Thanks

    Thank you very much brother that will help me a lot. Hoping to see you again..

    A friend in need is a friend in deed.

    Thank You
    Amit V
    A friend in need is a friend in deed

  4. #4
    Join Date
    Jan 2004
    Location
    Munich/Germany
    Posts
    12
    Just a tip

    If you are using slow bcp, you do not need this option to be turned on.


    Originally posted by fadace
    Code:
    bcp -help
    
    usage: 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]
            [-a display_charset] [-q datafile_charset] [-z language] [-v]
            [-A packet size] [-J client character set]
            [-T text or image size] [-E] [-g id_start_value] [-N] [-X]
            [-M LabelName LabelValue] [-labeled]
            [-K keytab_file] [-R remote_server_principal]
            [-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
    E.g.
    Code:
    bcp master..syslogins out syslogins.bcp -Usa -Pxxx -SServerName -c
    To bcp in, the option "select into/bcp" must be switched to "on"

    Code:
    use master
    go
    sp_dboption MyDB, "select into", true
    go
    use MyDB
    go
    checkpoint
    go

  5. #5
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Smile How to BCP the entire database

    Hi Again

    One last but one doubt.

    HOW TO BCP ALL THE TABLES IN THE DATABASE AT ONCE (ENTIRE DATABASE)

    Thankkkkkkkkkkkkkkkkk You
    Amit V
    A friend in need is a friend in deed

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    generate the bcp statements using

    Code:
    SELECT "bcp "+db_name+".."+name+" out "+name+".bcp -Usa -Pxxx -n -S"+@@servername
    FROM sysobjects
    WHERE type="U"

  7. #7
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Explanation Needed

    Dear firend,

    I need some clarification. Say i have three table in a database named DB_EMP the table names are TBL_NAME, TBL_ADD, TBL_TEL using this as an example please explain me how to export and then import these tables at once. Your help will be highly appreciated.

    I have about 700 tables to export and import the same

    Thank you,
    Amit
    Last edited by versoft; 01-14-04 at 05:43.
    A friend in need is a friend in deed

  8. #8
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    My select statement generates a bcp out command for each table of a specific database. Then you need to execute this output in a OS shell.

    for only 3 tables, I recommend to manually write the bcp cmds

    Export :
    Code:
    bcp DB_EMP..TBL_NAME out TBL_NAME.bcp -Usa -Pxxx -SServerName -c
    bcp DB_EMP..TBL_ADD out TBL_ADD.bcp -Usa -Pxxx -SServerName -c
    bcp DB_EMP..TBL_TEL out TBL_TEL.bcp -Usa -Pxxx -SServerName -c
    Import:
    Code:
    bcp DB_EMP..TBL_NAME in TBL_NAME.bcp -Usa -Pxxx -S2ndASE -c
    bcp DB_EMP..TBL_ADD in TBL_ADD.bcp -Usa -Pxxx -S2ndASE -c
    bcp DB_EMP..TBL_TEL in TBL_TEL.bcp -Usa -Pxxx -S2ndASE -c

  9. #9
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    I have about 700+ tables to export and import the same
    A friend in need is a friend in deed

  10. #10
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    So, use my select statement to generate the lines to execute !

  11. #11
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33
    Please explain me with the previous example. It would be so kind of you
    A friend in need is a friend in deed

Posting Permissions

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