Export table from database to csv file

Some ideas:

From SQL Server Management Studio

 1. Run a SELECT statement to filter your data 2. Click on the top-left corner to select all rows 3. Right-click to copy all the selected 4. Paste the copied content on Microsoft Excel 5. Save as CSV

Using SQLCMD (Command Prompt)

Example:

From the command prompt, you can run the query and export it to a file:

sqlcmd -S . -d DatabaseName -E -s, -W -Q "SELECT * FROM TableName" > C:\Test.csv

Do not quote separator use just -s, and not quotes -s',' unless you want to set quote as separator.

More information here: ExcelSQLServer

Notes:

• This approach will have the "Rows affected" information in the bottom of the file, but you can get rid of this by using the "SET NOCOUNT ON" in the query itself.

• You may run a stored procedure instead of the actual query (e.g. "EXEC Database.dbo.StoredProcedure")

• You can use any programming language or even a batch file to automate this

Using BCP (Command Prompt)

Example:

bcp "SELECT * FROM Database.dbo.Table" queryout C:\Test.csv -c -t',' -T -S .\SQLEXPRESS

It is important to quote the comma separator as -t',' vs just -t,

More information here: bcp Utility

Notes:

• As per when using SQLCMD, you can run stored procedures instead of the actual queries
• You can use any programming language or a batch file to automate this

Hope this helps.

Here is an option I found to export to Excel (can be modified for CSV I believe)

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [SheetName$]') select * from SQLServerTable

You can also use following Node.js module to do it with ease:

https://www.npmjs.com/package/mssql-to-csv

var mssqlExport = require('mssql-to-csv')    // All config options supported by https://www.npmjs.com/package/mssql     var dbconfig = {        user: 'username',        password: 'pass',        server: 'servername',        database: 'dbname',        requestTimeout: 320000,        pool: {            max: 20,            min: 12,            idleTimeoutMillis: 30000        }    };    var options = {        ignoreList: ["sysdiagrams"], // tables to ignore         tables: [],                  // empty to export all the tables         outputDirectory: 'somedir',        log: true    };    mssqlExport(dbconfig, options).then(function(){        console.log("All done successfully!");        process.exit(0);    }).catch(function(err){        console.log(err.toString());        process.exit(-1);   });