none
Export tables to CSV

    Question

  •  

    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.

    Thursday, July 12, 2007 2:22 PM

All replies

  • 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.
    Thursday, July 12, 2007 2:25 PM
  •  

     

    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.

    Thursday, July 12, 2007 2:27 PM
  • 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.

     

    Thursday, July 12, 2007 2:32 PM
  •  

    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.

    Thursday, July 12, 2007 2:38 PM
  • Have you explored the Export Wizard and determined if it would (or not) work in your situation?

     

    What Edition of SQL Server?

    Thursday, July 12, 2007 2:54 PM
  • 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.

     

     

    Thursday, July 12, 2007 3:09 PM
  • 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.

    Thursday, July 12, 2007 3:24 PM
  •  

    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

    Thursday, July 12, 2007 3:38 PM
  • 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.

    Thursday, July 12, 2007 4:18 PM
  •  

    The edition is Microsoft SQL Server Management Studio

    Thursday, July 12, 2007 4:27 PM
  • 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.

    Thursday, July 12, 2007 4:33 PM
  • SQL Server Express (sorry I really am not familiar with these products at all)
    Thursday, July 12, 2007 4:57 PM
  • 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.

     

     

    Thursday, July 12, 2007 5:08 PM
  • Try www.sqlscripter.com to export tables to csv/text.
    Saturday, July 21, 2007 12:51 PM