SQL Puzzle | The BCP-OUT with Field Headers Puzzle

In this puzzle you have to read data from a table and use Bcp Out command using xp_cmdshell command. The text file should have column names as well as data from the table. Please check out the sample input values and sample expected output below.

Sample Input

 1 Pawan 2 Sharlee 3 Jason 1 Pawan 2 Sharlee 3 Jason

Expected Output

Field1 Field2
1 Pawan
2 Sharlee
3 Jason
1 Pawan
2 Sharlee
3 Jason

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE test_SQL ( Id INT ,Val VARCHAR(100) ) GO INSERT INTO test_SQL VALUES ( 1,'Pawan' ) , ( 2 , 'Sharlee' ) , (3,'Jason' ) GO INSERT INTO test_SQL VALUES ( 1,'Pawan' ) , ( 2 , 'Sharlee' ) , (3,'Jason' ) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ``` -- SET NOCOUNT ON; DECLARE @filename varchar(150), @query varchar(5000), @bcp varchar(5000), @year char(4) = '2015', @month varchar(7) = 'March', @debug bit = 0 SET @filename = '"C:\Pawan\tblName_' + @month + '_' + @year + '.txt" -c -T' /* BELOW CODE should be on 1 LINE */ SET @query = '" SELECT ''''field1'''',''''field2'''' UNION ALL SELECT CAST(Id AS VARCHAR(15)),CAST(Val AS VARCHAR(100)) FROM HMIS_Mayank.dbo.test_SQL "' SET @bcp = ' EXEC master..xp_cmdshell '' bcp ' + @query + ' queryout ' + @filename + '''' EXEC ( @bcp ) SET NOCOUNT OFF; -- ```

Output

```--

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Starting copy...
NULL
7 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (7000.00 rows per sec.)
NULL

--
```

OUTPUT -Image text file

Text file showing data with Field Headers

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com