SQL Server Performance

How to append flat file using TSQL

Discussion in 'SQL Server 2005 General Developer Questions' started by AJITH123, May 27, 2009.

  1. AJITH123 Member

    Hi,
    How can we append an existing flat file with a query result [it may be more than one records.]
    i tried this way, however, it will delete the content of the flat file [.txt] rather than append :(
    xp_cmdshell "bcp mydb..vwTest out D: est.txt -Usa -Ppassword"
    Thanks
    AJITH123, May 27, 2009
    #2

  2. FrankKalis Moderator

    FrankKalis, May 27, 2009
    #3

  3. AJITH123 Member

    Hi frank,
    i saw the comments "BCP always overwrites any file that might already be there. There is no option to append [:(] " could you please suggest any solution? [dont want ssis].
    Thanks
    AJITH123, May 27, 2009
    #4

  4. FrankKalis Moderator

    Never tried that myself, but what about a batch file in which you bcp out to a dummy file and then append that dummy file to the final one. Should be as simple as
    echo. >> <finalfile>.csv
    FrankKalis, May 27, 2009
    #5

  5. Madhivanan Moderator

    [quote user="FrankKalis"]
    Never tried that myself, but what about a batch file in which you bcp out to a dummy file and then append that dummy file to the final one. Should be as simple as
    echo. >> <finalfile>.csv
    [/quote]
    That is the way to go.
    When I posted the script on how to export data to excel, so many users were asking me how to export data along with column headers.
    The only possible method I could think of is to use dummy file and append to main file

    FYI, refer point 5
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

    This should also apply to text files
    Madhivanan, May 28, 2009
    #6

Share This Page