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'
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!
Perfect thank you very much!
--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;
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.