Pipe Output

[moone009]

I've have been doing some research on how to export export a pipe delimiated file from sql management studio but I cannot quite seem to figure it out.

How would I simply turn this output into a piped file.

select * from #tmpRPLRouting  where propertydatamap = '3'

[BitBlt]

This assumes you are using SSMS 2008 or above.

First, open a new query window, then type your query, specifying the NOCOUNT option:

SET NOCOUNT ON
select * from #tmpRPLRouting  where propertydatamap = '3'

Then right-click and select Results To->Results To Text.
Then, right-click and select Query Options... (down at the bottom).
In the TreeView to the left, under Results, click on Text.
On the right, drop down the Output format: combo box and select "Custom Delimiter".
When the Custom Delimiter text box appears, type in the "|" (pipe) character.
Note: make sure the maximum number of characters is set appropriately for your data.
Click Ok.
Execute your query.
Once the query is complete (you should see a nice pipe-delimited result set) then right-click anywhere on the results and select "Save Results As...".
Navigate to the directory of your choice, give the file a file name and click Save.
That should do it. Open the resulting file with notepad to verify that it worked.

Good luck!

[moone009]

Perfect thank you very much!

[hrothenb]

--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;