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).
18 | DECLARE @sql varchar(8000) |
19 | DECLARE @sqlselect as varchar(8000) |
21 | DECLARE @Type as char(1) |
22 | DECLARE @Number as varchar(5) |
24 | DECLARE @datetoday as smalldatetime |
25 | DECLARE @directorylocation as varchar(200) |
26 | DECLARE @firstpartfilename as varchar(10) |
29 | DECLARE @YearString as varchar(4) /* i.e. 2017 */ |
30 | DECLARE @MonthString as varchar(2) |
31 | DECLARE @DayString as varchar(2) |
32 | DECLARE @MonthChar as char(2) |
33 | DECLARE @DayChar as char(2) |
35 | SET @Type = 'C' -- just a hard-coded constant |
36 | SET @Number = 726 -- just a hard-coded number (will have leading zeros) |
37 | SET @datetoday = getdate() |
38 | SET @directorylocation = 'C:\data\temp stuff\' |
39 | SET @firstpartfilename = 'authors' |
41 | SET @YearString = cast(DATEPART(yyyy, @datetoday) as varchar(4)); |
42 | SET @MonthString = cast(DATEPART(mm, @datetoday) as varchar(2)); |
43 | SET @DayString = cast(DATEPART(dd, @datetoday) as varchar(2)); |
44 | -- put leading zeros in fromt of month and day if the month |
45 | -- and day are only one character long (it sorts better that way) |
46 | SET @MonthChar = cast(right('00' + @MonthString,2) as char(2)) |
47 | SET @DayChar = cast(right('00' + @DayString,2) as char(2)) |
48 | --------------------------------------------------------------------------------- |
49 | DELETE FROM dbo.AuthorsFormatted |
50 | INSERT INTO dbo.AuthorsFormatted |
52 | cast('hello' as char(5)) |
53 | + cast(' world' as char(6)) |
54 | + cast (@Type as char(1)) AS OutputCol |
56 | SELECT cast([firstname] as char(10)) |
57 | + cast(' ' as char(1)) |
58 | + cast([lastname] as char(20)) AS OutputCol |
61 | SELECT cast('T' as char(1)) |
62 | + cast(right('00000' + @Number,5) as char(5)) |
63 | ---------------------------------------------------------------------------------- |
64 | SELECT * FROM dbo.AuthorsFormatted -- just display results to the SQL Server user: |
65 | ---------------------------------------------------------------------------------- |
66 | SET @sqlselect = 'SELECT Output FROM myDatabase.dbo.AuthorsFormatted' |
67 | SET @sql = 'bcp "' + @sqlselect |
71 | + @YearString + @MonthChar + @DayChar |
74 | PRINT N'sql string to be sent to xp_cmdshell to create output file: ' |
76 | 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.
13 | sql string to be sent to xp_cmdshell to create output file: |
14 | bcp "SELECT Output FROM myDatabase.dbo.AuthorsFormatted" queryout "C:\data\temp stuff\authors20170228.txt" -c -T -SDESKTOP-M9A1T3K\SQLEXPRESS |
16 | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
21 | Network packet size (bytes): 4096 |
22 | Clock Time (ms.) Total : 15 Average : (266.67 rows per sec.) |
