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

Expected Output

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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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