export data to pipe delimited file


  • Hi,

    I am trying to export the sql script data to pipe delimited text file.

    I did below steps

    1. JOined the sql tables and wrote the sql script.
    2. export the results to glbal temp table.
    3. then BCP the results to pipe delimited text file.

    here is the select query

    Select dbo.FN_Decrypt(@master, Login.LoginSkey,PatientDisplayID) as 'Client ID', ISNULL(SugarEntryCount,0) as 'BG Count',
        ISNULL(CarbsEntryCount,0) as 'Carb Count', ISNULL(MedsEntryCount,0) as 'Med Count'
    FROM #temp_patients inner join Login ON  Login.LoginID = #temp_patients.PatientID

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##PIPE_REPORT " queryout C:\Data\Objects.txt -t"|" -c -T '

    DROP TABLE #temp_patients

    I have successfully exported data into pipe delimited text file. but only data not the columns names

    I need columns names inthe first row, please let me know how can i do this?

    and also I need special characters enclosed in quotes. And how can I describe each column data type in text file? I will be having alphabets and numerics in text file.

    Thank you

    Wednesday, May 18, 2011 3:26 PM