exporting data from sql server into a CSV using ssms

sql, sql-server, sql-server-2008, ssms

Solution

You could run `xp_cmdshell` to run a `bcp` operation:

use [master];

declare @sql nvarchar(4000)
select @sql = 'bcp "select * from sys.columns" queryout c:\file.csv -c -t, -T -S'+ @@servername
exec xp_cmdshell @sql

You'd, of course, have to figure out how to format your qualifiers (probably through a format file)

EDIT:

Your source query would need to be something along the lines of:

SELECT IntValue + '"' + CharValue + '"' FROM TABLE

Also, you may need to have this feature enabled

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

Problem

I need to export data from several tables in SQL Server 2008 using SSMS. I do not want to use the native Export Data Wizard; I want to use a query instead. This means I cannot use `sqlcmd` or `bcp`. How can I export data out of SQL Server 2008 using a query? I need it to be comma delimited and double quoted as a text qualifier. Thanks so much for any guidance/help.

Original source