CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssms
Alex Gordon
Alex Gordon

Reputation: 60912

exporting data from sql server into a CSV using ssms

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.

Upvotes: 5

Views: 13744

Answers (5)

swasheck
swasheck

Reputation: 4703

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

Upvotes: 5

JamieSee
JamieSee

Reputation: 13030

You can easily create CSV output from SSMS, however it does not do quoting so you may want to choose a format like Tab delimited instead in step 6:

  1. Open a new query window.
  2. Create your SQL query.
  3. Right click in the query window.
  4. Choose Query Options...
  5. Choose Text under Results.
  6. Change the Output format: to Comma delimited.
  7. Change the Maximum number of characters displayed in each column to 8000 or an appropriate value.
  8. Click OK.
  9. Right click in the query window.
  10. Choose Results To and Results to File.
  11. Execute your query.
  12. Choose a file name and location.
  13. Click Save.

Upvotes: 11

Tom
Tom

Reputation: 1360

To export into an EXISTING excel file:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [SheetName$]') select * from SQLServerTable

This is dated, but I believe it is still valid.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Cheers!

Upvotes: 1

Janus007
Janus007

Reputation: 366

You could write a select spiced up with insert.

For instance: select 'insert into TargetTable values(id, name) values ('''+ id +''','''+ name +''')' from SourceTable

Upvotes: -1

Saurabh R S
Saurabh R S

Reputation: 3177

Creating a text file using a SQL query is not possible. SQL is meant only for fetching,parsring,updating(etc) the data from the database. You need to have sytem executables/dlls to write to a file.
Is there a specific reason why you want to use a SSMS to export the results produced to a csv? Why don't you use SSIS to generate the results for a csv?

If you use SSIS to do this you create a package to export the information and then you schedule the package in a job to run when ever you want it to.

Upvotes: 3

Related Questions