CODEWITHSUNDEEP
sql-serverexport
user2704338
user2704338

Reputation: 89

Export all SQL Server tables at once to txt or csv

I have hundreds of SQL Server tables to export to txt or csv with " text qualifier and | delimited. Import/Export wizard allows only one table at a time.

Is there any better method/tool/script to this all at once?

Thanks

Upvotes: 3

Views: 12549

Answers (3)

Elmar
Elmar

Reputation: 1246

I saw your post and looked at the Export tool in SQL management studio too, noticed it doesnt do the multi table export you're talking about (in my case the sql management studio 2016 preview). I thought perhaps I'd write a quick PowerShell script to do the job.

This does the job for me at least, 1. extracts a list of all the tables in the database, then 2. loops through all the table and 3. selects from the tables and exports to csv in the predefined location.

I executed via PowerShell ISE on windows 10, powershell version 5.

If you're unsure of your version run:

$PSVersionTable.PSVersion

Just remember to change your execution policy before running it e.g. https://technet.microsoft.com/en-us/library/ee176961.aspx

I made mine unrestricted before executing this script.

Set-ExecutionPolicy Unrestricted

Here's the script I wrote. 

$databaseName="<DATABASE_NAME>"
$instanceName="<HOSTNAME\INSTANCENAME>"
$baseExportPath="C:\temp1\dbexport"
$query = "SELECT name FROM sys.Tables"

$tableNames = Invoke-SqlCmd –ServerInstance $instanceName -Database $databaseName –Query $query

New-Item -Force $baseExportPath -type directory

foreach($dataRow in $tableNames)
{
   $exportFileName=$baseExportPath + "\\" + $dataRow.get_Item(0).ToString() + ".csv"

   $tableSpecificQuery="select * from " + $dataRow.get_Item(0).ToString()
   Invoke-SqlCmd –ServerInstance $instanceName -Database $databaseName –Query $tableSpecificQuery | Export-Csv -Path $exportFileName -NoTypeInformation
}

I didn't explicitly specify a delimiter for the export-csv function but it could be easily done by adding

-Delimiter '|'

Upvotes: 4

CaseyR
CaseyR

Reputation: 450

You could do something with BCP using the following:

SELECT 'bcp [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] out "' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt" -T -c -t"|" -d {DATABASE NAME} -S {SERVER NAME} -T'
FROM INFORMATION_SCHEMA.TABLES

This will output BCP statements for each of the tables in the database you run it against. You could then copy and paste those into a command window.

In the above, you'd want to replace {DATABASE NAME} and {SERVER NAME} with the details for your environment. The "-T" uses a trusted connection (i.e., you) so if you need to do something with a specific username and password, you'd need to adjust accordingly. Take a look at the BCP util write-up for more details.

The SELECT may require more tweaking based on the names of the objects in your DB but hopefully this gives you some idea of where/how to start.

Upvotes: 3

John Specko
John Specko

Reputation: 307

The Import/Export wizard creates and runs a bare-bones SSIS package. Use multiple data flow tasks in a package to do additional tables. A good start would be saving the package from the wizard and copy/pasting the task there. From there, change your data source and destination in each task.

Working in BIDS or SQL Data Tools isn't too bad for something simple like this.

https://msdn.microsoft.com/en-us/library/ms141122.aspx

Upvotes: 0

Related Questions