3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2704338
  • 89
  • 1
  • 1
  • 9

3 Answers3

3

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.

CaseyR
  • 437
  • 5
  • 13
  • This works great when trying to reverse engineer a strange database - I just dumped a few hundred tables after changing a specific value in the application to ABC123XYZ456. When I grepped for that value in the resulting text files, the table and column name to check became obvious. This is super helpful for reverse engineering - thank you! – Ian McGowan Sep 25 '17 at 05:33
  • I'm glad you found it useful! I had done something similar to you back in my Sybase days. FWIW, there are a few utilities out there that do something similar, one I've used is [ApexSQL Search](https://www.apexsql.com/sql_tools_search.aspx) – CaseyR Sep 26 '17 at 11:21
3

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 '|'
Elmar
  • 1,236
  • 1
  • 11
  • 16
0

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

John Specko
  • 197
  • 9