Is there a SQL function that allows me to export the query result to a .csv automatically?

Question

I'm trying to back up all the tables in a SQL Server Database. Here's the logic I'd like to execute:

For Each TableName in MyDatabase
SELECT * 
FROM TableName
Export Results to C:\TableName.csv
Loop

You will have a better experience here if you take the [Tour](https://stackoverflow.com/tour) and read through [How To Ask](https://stackoverflow.com/help/how-to-ask) and [What topics can I ask about here?](https://stackoverflow.com/help/on-topic) As written, this statement is off-topic. — Eric Brandt, Aug 03 '20 at 17:04
sql server has backup functionality OR the ability to save a query result to text, please clarify what exactly you want, a backup, a query result, something else? — Mark Schultheiss, Aug 03 '20 at 17:11
Try [BCP](https://docs.microsoft.com/en-us/sql/tools/bcp-utility?redirectedfrom=MSDN&view=sql-server-ver15) with the *queryout* option — John Wu, Aug 03 '20 at 17:22
Does this answer your question? [SQL Server Management Studio 2012 - Export all tables of database as csv](https://stackoverflow.com/questions/30791482/sql-server-management-studio-2012-export-all-tables-of-database-as-csv) — SMor, Aug 03 '20 at 17:25

Adam · Answer 1 · 2020-08-03T17:30:29.093

*Disclaimer that none of these will ever be a better option than a native backup in terms of backing of data. I'm assuming you simply want some flat file backups.

Several manual options are available from generating the scripts and data via Tasks->Generate Scripts, to manually running a select * from tablename and then right clicking -> Copy including header columns into their own excel files.

More automated options involve SSIS packages to handle this sort of enumerator logic. I believe that the best way to handle this would be a powershell script. Please see @Smor's comment to the following answer: SQL Server Management Studio 2012 - Export all tables of database as csv , for a detailed powershell solution.

The only T-SQL based querying options I'm aware of would be to attempt something of the following:

SELECT
    'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
    ''Excel 8.0;Database=@filepath\'' + t.name + '.csv '', --@filepath would be replaced with your logic for determining filename+path
    ''SELECT * FROM [SheetName$]'') 
    select * from ' + t.name + ';'
FROM
    sys.tables

And executing the results of this script.

Is there a SQL function that allows me to export the query result to a .csv automatically?

1 Answers1