How to export the result of an SQL query as a .csv file in SQL Management Studio (using code)

Question

I am new to SQL and I'd like to export the result of my query as a .csv file. This question was answered with a point and click solution.

I wish to automate this query such that it runs every day at 6 am. I come from an R background, so I'm wondering if there's a command similar to write.csv. Is it possible to simply write a command in the last line of the query to tell the computer to write the result as a .csv file in a given folder?

Gutzy · Accepted Answer · 2018-11-29 23:27:01Z

3

You can use the code below, but just make sure you already have a version of your csv file saved to your documents/desktop/etc.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [yourfile.csv]')
SELECT Field1, Field2, Field3, Field 4, Field 5 FROM DatabaseName

Edit: If you are having an issue with Microsoft.ACE.OLEDB.12.0 not being registered check out below link.

https://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

Example:

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Users\My Documents;HDR=YES;FMT=Delimited','SELECT * FROM [EmployeeInfo.csv]')
        SELECT
FirstName
,LastName
,DOB
 FROM Employees

edited Nov 29, 2018 at 23:27

answered Nov 29, 2018 at 18:17

Gutzy

10212 bronze badges

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

3 Comments

Arturo Sbr Over a year ago

Thank you @Gutzy. What do you mean SELECT {fields} FROM DatabaseName ? Should I be posting the command at the top of the query and treating the SELECT (and all other sections) as I normally would?

Arturo Sbr Over a year ago

Oh, and how do I specify the path to [yourfile.csv]?

Gutzy Over a year ago

Hi @ArturoSbr, please refer to example I added to my original comment. You can specify your path in the Database= in the Insert Into statement. Field is whatever you want it to be in your query. In my example I am pulling some fields from the Employee table in my database. Hope this helps!

Collectives™ on Stack Overflow

How to export the result of an SQL query as a .csv file in SQL Management Studio (using code)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related