How to export data from SQL server database to csv or text files

Question

I have 35 tables in a SQL server database and have to export the data from these tables to csv/text files. I tried to export using SQL Server Management Studio, but it allows only one table at a time which is really painful. Is there any other way to submit SQL/batch command to export all the tables?

Answer 1

As far as I know you can try the following two methods:

1. Using PowerShell script

Create a .ps1 file with the following statements-

$server = "<database_server>"
$database = "<database_name>"
$tablequery = "SELECT name from sys.tables"

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Uid=XXXXXX;Pwd=XXXXXX;Initial Catalog={1};Connection Timeout=0;"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
$command.CommandTimeout = 0

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])]"

    #Specify the output location of your dump file
    $extractFile = "C:\XXX\$($Row[0]).csv"

    $command.CommandText = $queryData
    $command.Connection = $connection
    $command.CommandTimeout = 0

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation
}

Replace the information highlighted in blue with your information and run the script file from PowerShell command window. If you have gigantic tables (in 100's GB or TB) and your system has limited RAM, the above script will give memory exception error and all the records from the table will not be exported. I would recommend to use the second method described below.

2. Using bcp command

Create a .bat file and type the following statement for all tables.

start bcp databasename.schemaname.tablename out C:\XXX\tablename.csv -c -U <username> -P <password> -S <servername\instancename>

Replace the text highlighted in blue with your information and run the batch file from Windows command line. This method copies all the records from table. It will not copy the column names.