Post reply

8 Ways to Export SQL Results To a Text File

  • October 25, 2016 at 9:27 pm

    #331288

    Comments posted to this topic are about the item 8 Ways to Export SQL Results To a Text File

  • October 26, 2016 at 12:21 am

    #1908814

    Excellent article! Really enjoyed it. Thanks for taking the time to write that bad boy.

    My only gripe would be this...

    From the article:

    Use it when you have millions of rows to copy files. It is a very fast option specialized in exporting and importing data from multiple sources.

    The only thing missing from the article is that... a performance test of a million fairly wide rows for each method.

    I'll also say that the Powershell output is a bit rough without any formatting added.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 26, 2016 at 3:34 am

    #1908826

    good article. worth pointing another very easy way, cut and paste. Cut from SSMS and paste into Excel. I use this a lot for simple results. Especially useful with registered servers since the output is not just a straight query result, it has the server name as well

  • October 26, 2016 at 6:25 am

    #1908857

    Thanks very much for a great article. Some of the methods I knew, but others were an education for me. Appreciate your time and efforts!

  • October 26, 2016 at 6:32 am

    #1908858

    Nice consolidation of the various methods.

  • October 26, 2016 at 6:37 am

    #1908862

    Nice article.

    One extra point to consider, is the expected length of your data. The bcp utility, for example, can handle huge strings, but ssms and sqlcmd truncates the output.

  • October 26, 2016 at 7:29 am

    #1908883

    You can also export text files very easily from SQL Server using an ODBC text database. Hmm. Perhaps I should write an article about that, but I show you how to do it here

    https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/

    Best wishes,
    Phil Factor

  • October 26, 2016 at 7:55 am

    #1908895

    You can also highlight all or some of the cells in an SSMS Results grid, right-click and choose "Save Results As..."

    --
    Scott

  • October 26, 2016 at 8:32 am

    #1908917

    Phil Factor (10/26/2016)

    You can also export text files very easily from SQL Server using an ODBC text database. Hmm. Perhaps I should write an article about that, but I show you how to do it here

    https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/%5B/quote%5D

    I love that article, Phil. You wrote one before for the old Jet drivers for similar tactics.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 26, 2016 at 9:04 am

    #1908921

    I find PowerQuery handy. It's an Excel add-in from Microsoft. It can read SQL Server, SharePoint, flat files, web services, even FaceBook.

  • October 27, 2016 at 12:41 pm

    #1909210

    For results to file, I have to Cast the columns to a shorter length than what they because, at least for .txt, a Varchar(200) will output a nice underlined column header for 200 characters :crazy:

    This has the effect of wrapping the text file output a few times around; tough to eyeball.

  • October 27, 2016 at 3:37 pm

    #1909248

    ken.trock (10/27/2016)

    For results to file, I have to Cast the columns to a shorter length than what they because, at least for .txt, a Varchar(200) will output a nice underlined column header for 200 characters :crazy:

    This has the effect of wrapping the text file output a few times around; tough to eyeball.

    What are you using to do the export with?

    EDIT: Never mind. I see it. "Results to File".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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