exporting data from sql server into a CSV using ssms

Question

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.

@swasheck thank you, i meant to say the reason for a query is because i need to automate this and passing parametersinto query — Alex Gordon
– Alex Gordon, Commented Jun 13, 2012 at 18:19
possible duplicate of exporting by year and month from 100 tables — George Stocker
– George Stocker, Commented Jun 13, 2012 at 23:01

JamieSee · Accepted Answer · 2012-06-13 18:24:12Z

11

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:

Open a new query window.
Create your SQL query.
Right click in the query window.
Choose Query Options...
Choose Text under Results.
Change the Output format: to Comma delimited.
Change the Maximum number of characters displayed in each column to 8000 or an appropriate value.
Click OK.
Right click in the query window.
Choose Results To and Results to File.
Execute your query.
Choose a file name and location.
Click Save.

answered Jun 13, 2012 at 18:24

JamieSee

13.1k2 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Gordon Over a year ago

i need this automated because i need to pass parameters into the query

swasheck · Accepted Answer · 2012-06-13 21:41:25Z

5

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

edited Jun 13, 2012 at 21:41

answered Jun 13, 2012 at 18:27

swasheck

4,7232 gold badges32 silver badges58 bronze badges

8 Comments

Alex Gordon Over a year ago

thank you very much. my i CAN do a sqlcmd without a format file? i dont want to use a format file, is this posible?

swasheck Over a year ago

sure, but you'll have to put a lot more work in to put in text qualifiers

Alex Gordon Over a year ago

great! can you get me started?

swasheck Over a year ago

It really just becomes a matter of formatting a query such that it includes " where you need it.

jcolebrand Over a year ago

@АртёмЦарионов it is not considered polite to ask people here to login to your box for a gotomeeting to review your code. We need interaction to occur here on this site, so that people can have answers for their problems. Encouraging someone to come login to your box doesn't help the next person who has this same problem. And if your problem is that unique, maybe you're working on solving the wrong problem?

|

Saurabh R S · Accepted Answer · 2012-06-13 18:04:48Z

3

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.

answered Jun 13, 2012 at 18:04

Saurabh R S

3,1871 gold badge38 silver badges46 bronze badges

2 Comments

Alex Gordon Over a year ago

thank you - how would i pass parameters into a query this way?

Saurabh R S Over a year ago

If you adopt the SSIS approach, a simple select query will do the task. See this if you wish to learn how to do this using SSIS.

Tom · Accepted Answer · 2012-06-13 18:14:50Z

1

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!

answered Jun 13, 2012 at 18:14

Tom

1,36011 silver badges21 bronze badges

2 Comments

Tom Over a year ago

No. However, you could use automation to change the xls to csv. Why CSV over xls?

Alex Gordon Over a year ago

xls will not support the amount of rows that i need

Janus007 · Accepted Answer · 2012-06-13 18:07:22Z

-1

You could write a select spiced up with insert.

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

answered Jun 13, 2012 at 18:07

Janus007

3664 silver badges11 bronze badges

Collectives™ on Stack Overflow

exporting data from sql server into a CSV using ssms

5 Answers 5

1 Comment

8 Comments

2 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

8 Comments

2 Comments

2 Comments

Comments

Related