Unload to txt file?

  • Hello,

    I use to work with informix and was wondering if anyone knows the way in tsql to unload to a file

    in informix it was 

    UNLOAD TO '/tmp/dump.txt'  SELECT * FROM tablename;

     

    Thanks in advanced

  • In SQL Server there are a couple of choices however, if you wish to do it via T-SQL then you will have to use BCP.

    Have a look in BOL for the syntax.


    Kindest Regards,

  • Trigger,

    How do you use BCP from T-SQL without using xp_CmdShell which is normally off limits to all but those members of the SA role? 

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • osql from the command line with a script might be easier, but it's not T-SQL.

  • bcp isn't by far T-SQL either, it's a commandline exe just like isql and osql. You do need to use xp_cmdshell for all of these if you want to exec them from T-SQL code.

    BULK INSERT is T-SQL, however it only does load from file, not unload to file.

    xp_cmdshell by default is sa/sysadmin only privs required, however you can use the proxy account for non-admin users to use xp_cmdshell

    /Kenneth

  • Steve and Ken...

    Yup, I knew that... I just thought that since Trigger recommended it as a T_SQL command, there might be some neat trick he was using to use it without xp_CmdShell and I wanted to know what the trick was...

    Trigger? 

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • No new trick! You have to use xp_cmdshell.

    I have perfromed this many times. However, I restrict this process to the DBA's not the end users.


    Kindest Regards,

  • That's what I thought... thanks for the feedback, Trigger.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply