Post reply

Output result set to a .CSV file with TSQL

  • July 30, 2003 at 5:06 am

    #82311

    Hi

    Can anyone tell me the TSQL statements to place the result set of a query into a .csv file. I think I can do it through SQL Server 2000 --> Query Analyzer using the 'Tools' menu option but I really need the hard code.

    Any reply appreciated.

    Thanks

    Nathan

  • July 30, 2003 at 5:36 am

    #467698

    You can use the BCP command line utility to import and export data to a (formatted) file. Not exactly T-SQL of course, but using xp_cmdshell it is possible.

    I think you could use a Linked Server to an ODBC connection. If you construct a text file ODBC connection, you can do 'whatever you want' on the dataset in the file.

  • July 30, 2003 at 4:33 pm

    #467863

    You can run BCP from TSQL with xp_cmdshell:

    exec master.dbo.xp_cmdshell 'bcp "<query>" queryout <path1>\<csvfile.ext> -n -S<server> -T -o <path2>\<logfile.ext>'

    <query> = the complete SELECT statement. If it is a large query then turn it into a view and select from the view.

    <path1>\<csvfile.ext> = the path & filename to store the text file LOCAL TO THE SERVER THAT RUNS THE QUERY. If you use <drive letter>:\<path> syntax, it should be the local path on the server to a shared folder, but it is better to user URL \\destination\share\path syntax.

    <server> = the server that has the database. If you run this in Query Analyzer and you're connected to server A, it will run the BCP job which is free to connect to a different server. All the <path> values are relative to server A.

    <path2>\<logfile.ext> = the path & filename to store the output of the BCP command. If you've run BCP from the command line, you've seen it echo a line for every 1000 rows transferred. The -o switch tells BCP to save this crap to a file, otherwise it would all be returned as the result of the exec statement. Like the other path, it has to make sense on the server running BCP, but it doesn't have to be a share if you aren't going to look at it. You can use "exec master.dbo.xp_cmdshell 'del <path2>\<logfile.ext>'" to get rid of it.

Viewing 3 posts - 1 through 2 (of 2 total)

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