question

Doug avatar image
Doug asked

Output query results to csv files

I have a query that returns 4 select result sets. Is there a way to output the results into 4 csv files from the query without having to click on each result set and "save result as" and selecting CSV file? Please advice if there is a better way of producing the csv files. thanks, Doug
sql-server-2005sqlt-sqlsql-server
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
That's something you can do in my [SQL Server IDE][1] - right click a result set and say 'save all to CSV'. Not sure how you would do that in SSMS though. Funnily enough, I don't use it that much :) [1]: http://www.atlantis-interactive.co.uk/products/sqleverywhere/default.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image
ozamora answered
BCP using queryout
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Doug avatar image
Doug answered
Is BCP using Queryout is only in windows command line? I try to run it in sql management studio and I get a incorrect syntax. I query the results into a temp table and ran the following script BCP " Select * from #Result1 Order by [Territory] ,[selltoCustCountryCode] ,Year ,CustCount " queryout Test.csv -c -T
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes. You can execute using xp_cmdshell if the feature is enabled for the instance.
0 Likes 0 ·
Doug avatar image
Doug answered
I've enabled xp_cmdshell and try to run the below statement but I'm coming up with an error: Msg 155, Level 15, State 1, Line 3 'BCP' is not a recognized ODBC date/time extension option. This is new territory for me, can you please review the code below and let me know where I error out? EXEC xp_cmdshell { BCP ' Select * from #Result1 Order by [Territory] ,[selltoCustCountryCode] ,Year ,CustCount ' queryout Test.csv -c -T }
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.