Many times you may find that you need to take some data in a table or tables and export that data, in a specific format, to an external text (flat) file, often for the purpose of sending that file to another computer system, perhaps at another department or company.

This post discusses, by example, how to do that. There is an article on the Web called Exporting Data Programatically with bcp and xp_cmdshell that discusses how this works. Here is another article called Working with the bcp Command-line Utility.

Admittedly the following code is not very simple and is a bit longer that I would like it to be, but in the real world your code will be likely be more complicated and longer than this code.

Below are three screen shots of the original Authors table contents, the contents of the intermediate table called AuthorsFormatted, and a screen shot of the data of the outputted file as seen in NotePad++ after the data is selected.

Below is the SQL Server query code used in this example. The AuthorsFormatted table was already created at an earlier time. It has one column called [Output] with a data type of char(20).

USE myDatabase
GO
-- Purpose: To create a text output file from a table.
-- Inputs: dbo.Authors table
-- Outputs: a re-formatted text file 
--
-- Comments:
-- We need to create an intermediary table to hold the
-- formatted data from dbo.Authors because we are using
-- parameters. bcp and xp_cmdshell won't work otherwise.
-- The formatted one-column table: dbo.AuthorsFormatted
--
-- The dbo.Authors table is one I created that only has 2
-- records(rows) and 2 columns: [FirstName] and [LastName]
--
-- The output text file has a header, detail lines and a trailer.
--
DECLARE @sql varchar(8000)  -- for bcp
DECLARE @sqlselect as varchar(8000)  -- for xp_cmdshell
-- data used inside the output file:
DECLARE @Type as char(1)
DECLARE @Number as varchar(5)
-- data used in the output directory and file name:
DECLARE @datetoday as smalldatetime
DECLARE @directorylocation as varchar(200) -- of output text file
DECLARE @firstpartfilename as varchar(10)
-- We will include the current date in the text file name
-- We have the date (@datetoday) already
DECLARE @YearString as varchar(4)   /* i.e. 2017 */
DECLARE @MonthString as varchar(2)
DECLARE @DayString as varchar(2)
DECLARE @MonthChar as char(2)  -- will have leading zeros
DECLARE @DayChar as char(2)  -- will have leading zeros
--
SET @Type = 'C'  -- just a hard-coded constant
SET @Number = 726  -- just a hard-coded number (will have leading zeros)
SET @datetoday = getdate()
SET @directorylocation = 'C:\data\temp stuff\'
SET @firstpartfilename = 'authors'
--
SET @YearString = cast(DATEPART(yyyy, @datetoday) as varchar(4));
SET @MonthString = cast(DATEPART(mm, @datetoday) as varchar(2));
SET @DayString = cast(DATEPART(dd, @datetoday) as varchar(2));
-- put leading zeros in fromt of month and day if the month
-- and day are only one character long (it sorts better that way)
SET @MonthChar = cast(right('00' + @MonthString,2) as char(2))
SET @DayChar = cast(right('00' + @DayString,2) as char(2))
---------------------------------------------------------------------------------
DELETE FROM dbo.AuthorsFormatted
INSERT INTO dbo.AuthorsFormatted
	SELECT 
		cast('hello' as char(5))
		+ cast(' world' as char(6)) 
		+ cast (@Type as char(1)) AS OutputCol
	UNION ALL
	SELECT cast([firstname] as char(10))
		+ cast(' ' as char(1))
		+ cast([lastname] as char(20)) AS OutputCol
		FROM dbo.Authors
	UNION ALL
	SELECT cast('T' as char(1))
		+ cast(right('00000' + @Number,5) as char(5))
----------------------------------------------------------------------------------
SELECT * FROM dbo.AuthorsFormatted -- just display results to the SQL Server user:
----------------------------------------------------------------------------------
SET @sqlselect = 'SELECT Output FROM myDatabase.dbo.AuthorsFormatted'
SET @sql = 'bcp "' + @sqlselect 
		+ '" queryout "'
		+ @directorylocation
		+ @firstpartfilename
		+ @YearString + @MonthChar + @DayChar
		+ '.txt" -c -T -S'
		+ @@servername
PRINT N'sql string to be sent to xp_cmdshell to create output file: '
PRINT @sql
EXEC master..xp_cmdshell @sql

When the above script is run the results output in the SQL server query window is as follows. It will appear a little different in the query window itself because we are using word-wrap in this blog post on WordPress whereas no word-wrap is used in the SQL Server Results window.

(4 row(s) affected)

(4 row(s) affected)
Output
--------------------
hello worldC        
John       Smith    
Sally      Jackson  
T00726              

(4 row(s) affected)

sql string to be sent to xp_cmdshell to create output file: 
bcp "SELECT Output FROM myDatabase.dbo.AuthorsFormatted" queryout "C:\data\temp stuff\authors20170228.txt" -c -T -SDESKTOP-M9A1T3K\SQLEXPRESS
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Starting copy...
NULL
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (266.67 rows per sec.)
NULL

(7 row(s) affected)