export talbe into pipe-delimited .txt file

Last Post 08 Mar 2013 11:00 AM by hrothenb. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
USB15
New Member
New Member

--
11 Mar 2004 06:47 AM
I have a table that I need to export to a pipe delimited .txt file. I've been doing it from Enterprise Manager, but i would like to automate the process more by writing it into my T-SQL script. Can anyone help me to do this?

Thanks in advance.
USB15
New Member
New Member

--
11 Mar 2004 06:55 AM
What switches would be used to make the file pip-delimited?
USB15
New Member
New Member

--
11 Mar 2004 07:09 AM
So it would look something like:

bcp spa_231.extract_231 out c:\temp\spa_extract_231.txt -t|

Is that correct? Do I need to enclose the pipe in double quotes?
USB15
New Member
New Member

--
11 Mar 2004 08:05 AM
Can this be run in Query Analyzer or does it have to be from a command prompt?

If it is command prompt only, is there a way I can do the same thing in Query Analyzer?
hrothenb
New Member
New Member

--
08 Mar 2013 11:00 AM

--Generate BCP DOS command to export pipe delimited data.

DECLARE @BCPCommandString VARCHAR(8000)
DECLARE @FilePath VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
DECLARE @SPName VARCHAR(1000)
DECLARE @ServerName VARCHAR(1000)
DECLARE @tSQL VARCHAR(1000)

SET @ServerName = 'myServer'
SET @tSQL = 'Select top 10 * from myTable'
SET @FilePath = 'C:\temp\'
SET @FileName = 'myFileName.tmp'

SET @BCPCommandString = 'bcp "' + @tSQL + '" queryout ';

SET @BCPCommandString = @BCPCommandString + @FilePath + @FileName;

SET @BCPCommandString = @BCPCommandString + ' -S ' + @ServerName;

SET @BCPCommandString = @BCPCommandString + ' -T -c -t^| -U username -P password';

SELECT @BCPCommandString;


Acceptable Use Policy
---