Post reply

I need to write a SQL SELECT stmt that puts the results out to a CSV file in SQL 2008

  • March 28, 2013 at 5:44 am

    #272624

    I need to write a SQL SELECT stmt that puts the results out to a CSV file in SQL 2008. Will the SELECT INTO OUTFILE stmt work in SQL 2008? Here's what I'm thinking:

    SELECT field1, field2, field3 FROM database.tablename INTO OUTFILE flatfile.csv FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’

    Will that accomplish what I'm looking to do? Never tried this before...

  • March 28, 2013 at 5:56 am

    #1601573

    the command you posted is from MYSQL;

    SQL doesn't do file writing operations directly, you typically would use BCP OUT, or a CLR, or an application to write to disk when it comes to MS SQL Server.

    it can do file Reading operations via BULK INSERT (there is no BULK OUT equivalent natively)

    here's a BCP example, for a comma delimited file with CarriageReturn+LineFeed as the row delimiter: note you need to modify the query to have the databasename.schema.tablename in this example

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM BFONRA.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"," -r"\n" -T'

    I put together a proof of concept CLR suite if you want to add something like that:

    http://sqlclrexport.codeplex.com/

    a code example for that is like this:

    EXECUTE CLR_ExportQueryToCSV @QueryCommand = 'SELECT * FROM Products WHERE Territory = 42',

    @FilePath = 'C:\Data',

    @FileName = '\Products_export.csv',

    @IncludeHeaders = 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 28, 2013 at 6:06 am

    #1601578

    If it is one off then you can use Sql Studio to export the results to a file.

    Menu->Query->ResultTo->File.

  • March 28, 2013 at 6:07 am

    #1601579

    As a second thought, Use BCP (Bulk Copy Program)

  • March 28, 2013 at 6:34 am

    #1601592

    These will be called by a scripted scheduling program... so the BCP sounds like the answer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply