Export tables to CSV

Hello all. Is it possible using Transact-SQL to export data tables from SQL to CSV format?

I have trawled through the forum looking for an answer to this but can not find anything that provides a solution. I can export the tables individually by querying them then selecting 'save results as' but I would like to automate this (I have around 60 to do).

Is it possible to do this in T-SQL?

Any help greatly appreciated.

It would help if you describe the environment you have in mind. Give a look to using either BCP or SSIS. To me this sounds like a good use of SSIS.|||

Check out BCP in Books Online (BOL)

It's a cmdline tool, but can also be invoked from T-SQL using master.dbo.xp_cmdshell '...'

Being a cmdline tool, you can create a .bat file to run all your requests in one fell swoop.

With BCP you can dump an entire table (out option) or the results of a given query (queryoutoption).

BTW, query this forum for BCP and you'll find a number of posts about how to do it.

|||

I am using Microsoft SQL Server Management Studio - I am not overly familiar with this and it looks quite limited as to functionality. I have tried BCP commands but it complains about stored procedures not being present, however, this is something with which I am unfamiliar.

|||

It's the output of Stored Procedures that you are trying to export?

In that case you'll have to load the output of the SP into a temp table and then select out the contents of the temp table.

|||

Have you explored the Export Wizard and determined if it would (or not) work in your situation?

What Edition of SQL Server?

|||

Edition is SQL Server.2005.

I can not find an export wizard, the only way I have found to export the data so far is to query the table, right click in the results grid then select 'Save Results As' - this gives me an option to save as an export file.

|||

Using SQL Server Maintenance Studio (SSMS), in the Object Explorer pane, right-click on the database name, select [Tasks], then [Export Data...]

Follow the prompts and supply the information requested.

|||

I have found the export wizard, followed the steps but am getting an error message

TITLE: SQL Server Import and Export Wizard

Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Destination - Test_csv" (130).

BUTTONS:

OK

This occurs at the 'Validating' stage of the export (the sixth step in the process)

I have specified C:\Test.csv as a destination flat file for an initial try

|||

When I asked what Edition of SQL Server you are using, I wanted to know if you were using SQL Server Express or Standard or Enterprise, etc.

I understand that the version is SQL Server 2005.

|||

The edition is Microsoft SQL Server Management Studio

|||

Management Studio (SSMS) is ONLY a 'tool' to be used with a SQL Server Edition and version 2005 (and up.)

But you still haven't told me if you are using SQL Server Express, or Standard, or Workgroup, or Developer, or Enterprise Edition.

Using Management Studio, in the Object Explorer pane, right-click on the Server name, and select [Properties]. When the property window opens, the edition will be on the second line. Please post that information.

|||SQL Server Express (sorry I really am not familiar with these products at all)|||

That's ok. We all have to start somewhere.

You may need to download the SQL Server Express Toolkit from this location.

In the toolkit, you will find DTSWizard.exe. It is a separate program (put a shortcut on your desktop). DTSWizard should provide you the missing functionality.

|||Try www.sqlscripter.com to export tables to csv/text.