Generating CSv through a query in sql

Question

I have created a database, a table, and entries in that table through basic SELECT and INSERT INTO commands.

To view the entries I am using the basic query:

USE test1
SELECT * FROM orders

where test1 is Database and orders is Table name.

I can see the entries.

How can I store the results to a CSV?

With the query

SELECT * FROM orders
INTO OUTFILE '/path/to/file.csv'
FIELDS ESCAPED BY '""'
TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'"

I am getting an error

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'INTO'.

Judging from the error message, you're using SQL Server - right? There is no INTO OUTFILE ..... command in T-SQL .... — marc_s
– marc_s, Commented Aug 19, 2014 at 11:13
Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 ) Microsoft Data Access Components (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255) Microsoft MSXML 3.0 4.0 6.0 Microsoft Internet Explorer 9.0.8112.16421 Microsoft .NET Framework 2.0.50727.4984 Operating System 6.1.7600 — KUMAR SATYAM
– KUMAR SATYAM, Commented Aug 19, 2014 at 11:48

Community · Accepted Answer · 2017-05-23 10:27:58Z

0

You do have the option to output to csv from within management studio, you can return your grid as normal then right click and click "Save results As..."

or

Using SQLMD as per question How to export SQL Server 2005 query to CSV

sqlcmd -q "select col1,col2,col3 from table" -oc:\myfile.csv -h-1 -s","

Alternative is create it into a variable and then save that via your application e.g.

declare @csv varchar(max) = ""
select @csv += replace(column1, ',','","') + ', ' + replace(column1, ',','","') + char(13) + char(10) from orders
select @csv csv

Regards

Liam

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Aug 19, 2014 at 12:37

Liam Wheldon

7751 gold badge5 silver badges20 bronze badges

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

Comments

Kris Gruttemeyer · Accepted Answer · 2014-08-19 12:40:16Z

-1

If you're not as familiar with SQL and would like a more graphical based approach, you could always use the Import/Export Wizard. Little more user-friendly to people who may be new to SQL.

answered Aug 19, 2014 at 12:40

Kris Gruttemeyer

8727 silver badges19 bronze badges

Collectives™ on Stack Overflow

Generating CSv through a query in sql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related