1

I need a bit sql to export a set of tables 100+ to csv files.Each table will be a csv file

How do you do it in sql?

I can Import data into sql server using "Bulk Insert" is there an equivalent to convert a sql server table into CSV?

Open to Free tools as well

Do I have to reinvent the wheel and write my own little c# smo app to loop through all the tables and write a csv file?

many thanks for your help and suggestions

user9969
  • 15,632
  • 39
  • 107
  • 175
  • If you have SQL Server, you can use the `Data Import / Export` facility in Management Studio, which is using SQL Server Integration Services under the covers. Or you can use the command-line `Bcp` utility to run a `SELECT` statement and output the results to CSV – marc_s May 04 '14 at 07:40
  • In Management studio can you do multiple tables at once? how do you the command line bcp to process 100 table at once. – user9969 May 04 '14 at 07:43
  • No, these operations work on a table at a time - but if you call `bcp` from a batch file, you can call it 100 times, once for each table you need – marc_s May 04 '14 at 07:46
  • @marc_s yes that is the idea.any link with a noddy example? – user9969 May 04 '14 at 07:54

2 Answers2

1

You can employ Import and Export Wizard built into SQL Server Management Studio that will allow you to export/import a number of tables without writing any code.

You can find the overview in this Technet article. There is a series of articles there below that goes through each step required to complete the task.

bartover
  • 428
  • 2
  • 8
0

As @scottm said in this link you can use this command to export data to csv file .

You can run something like this:

sqlcmd -S MyServer -d myDB -E -Q "select col1, col2, col3 from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700
  • -h-1 removes column name headers from the result
  • -s"," sets the column seperator to ,
  • -w 700 sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)

And Export table from database to csv file is very useful link.

Finally You can see those solution from this link.

Community
  • 1
  • 1
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144