Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

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

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

Advertisements