I have to export some data from SQL Server 2012. I'm interested in flat file export (i.e.: .csv) but my database has about 500 tables.

Here is an example for you to better understand the situation:

  • Database: ETTEDB_list

  • Table1: list_20140101

  • Table2: list_20140102
  • .
  • ..
  • ...
  • TableN: list_20151706

All these tables have the same structure (number of columns, column names, data types).

Is there any way to automatically export all these tables into .csv files for later joining into one ore more BIG .csv files?

up vote 2 down vote accepted

You have three options.

  1. Use the gui of SQL Server Management Studio.
  2. Use BCP to export your data in a loop (see here)
  3. Use Powershell and script your export in a loop for example.

Here an example in PowerShell:

 $Tables = invoke-sqlcmd -query "SELECT name FROM sys.tables WHERE name LIKE 'list[_]%'" -database "ETTEDB_list" -serverinstance "YOURSERVER"
 foreach ($Table in $Tables)
 {
     $TableName = $Table["name"]
     write-host -ForegroundColor White "Creating File $TableName.csv"
     invoke-sqlcmd -query "SELECT * FROM $TableName" -database "ETTEDB_list" -serverinstance "YOURSERVER" |export-csv -path c:\scripts\$TableName.csv
 }

You'll need to adjust "YOURSERVER" to the right value and maybe change the path property to the correct folder. Hopefully this will help.

  • Would you please consider me a newbie in Powershell/MS SQL Server and point me straight to an already written script or command to do what am i supposed to? – Sir.pOpE Jun 17 '15 at 13:53
  • I've added a PowerShell example. – Ionic Jun 17 '15 at 14:08
  • 1
    Also if you want to put it all in a single CSV in the end anyway you could use dynamic SQL to create a UNION ALL view and just use any of the above tools to export the contents of the view. – Kenneth Fisher Jun 17 '15 at 14:44

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.