Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 export data to pipe delimited text file
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

332 Posts

Posted - 05/18/2011 :  12:25:57  Show Profile  Reply with Quote

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 global temp table.
3. then BCP the results to pipe delimited text file.

Here is my 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 in the first row, please let me know how can i do this?

Thank you

Most Valuable Yak

15732 Posts

Posted - 05/18/2011 :  12:29:42  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can get column headings using sqlcmd, but you also get a separator line that you can't suppress, you'd have to remove it later. The syntax would be:

EXECUTE master.dbo.xp_cmdshell 'sqlcmd -S. -E -Q"SELECT * FROM ##PIPE_REPORT" -oC:\Data\Objects.txt -s"|" -W'
Go to Top of Page

Not Just a Number

15586 Posts

Posted - 05/18/2011 :  12:41:12  Show Profile  Reply with Quote
How about

			  [Client ID]	varchar(50)
			, [BG Count]	varchar(50)
			, [Carb Count]	varchar(50)
			, [Med Count]	varchar(50)

			  [Client ID]
			, [BG Count]
			, [Carb Count]
			, [Med Count]
SELECT 		  [Client ID]
			, [BG Count]
			, [Carb Count]
			, [Med Count]
  FROM (
	SELECT	  'Client ID'		AS [Client ID]
			, 'BG Count'		AS [BG Count]
			, 'Carb Count'		AS [Carb Count]
			, 'Med Count'		AS [Med Count]
			, 0					AS Row_Order
	SELECT	  CONVERT(varchar(50),dbo.FN_Decrypt(@master, Login.LoginSkey,PatientDisplayID))	
								AS [Client ID]
			, CONVERT(varchar(50),COALESCE(SugarEntryCount,0))									
								AS [BG Count]
			, CONVERT(varchar(50),COALESCE((CarbsEntryCount,0))									
								AS [Carb Count]
			, CONVERT(varchar(50),COALESCE((MedsEntryCount,0))									
								AS [Med Count]
			, 1					AS Row_Order
	  FROM   #temp_patients 
INNER JOIN	 [Login] 
		ON   [Login].[LoginID] = #temp_patients.PatientID
ORDER BY Row_Order



Hint: Want your questions answered fast? Follow the direction in this link

Want to help yourself?

Edited by - X002548 on 05/18/2011 12:44:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000