CODEWITHSUNDEEP
sqlexport-to-csv
Arturo Sbr
Arturo Sbr

Reputation: 6323

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

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?

Upvotes: 1

Views: 4955

Answers (1)

Gutzy
Gutzy

Reputation: 92

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

Upvotes: 2

Related Questions