Solved

Xp_cmdshell/bcp - How to add header & trailer records

Posted on 2012-03-28
3
1,817 Views
Last Modified: 2012-04-10
Hi there,
I have tables with rows in millions.
I wish to use BCP for speed, I feel ssis or any other method will be very slow.

I am extracting data into flat files using bcp but I need to now add a header and trailer records. How can I do it using BCP?


-  xp_cmdshell to extract data
EXEC xp_cmdshell 'bcp "SELECT * FROM test1 " queryout "C:\test1.txt" -T -c -t,'
EXEC xp_cmdshell 'bcp "SELECT * FROM test2 " queryout "C:\test2.txt" -T -c -t,'
EXEC xp_cmdshell 'bcp "SELECT * FROM test3 " queryout "C:\test3.txt" -T -c -t,'

Thanks
0
Comment
Question by:crazywolf2010
3 Comments
 
LVL 2

Assisted Solution

by:Kelden
Kelden earned 250 total points
ID: 37777010
Is the header and trailer always the same? You could do it like

EXEC xp_cmdshell 'bcp "SELECT * FROM test1 " queryout "C:\test1.txt" -T -c -t,'
EXEC xp_cmdshell 'copy "c:\header.txt" + "C:\test1.txt" + "C:\trailer.txt" "C:\test1_1.txt"'

this should add the header and trailer and copy it to a new file text1_1.txt
0
 

Author Comment

by:crazywolf2010
ID: 37777087
Hi,
The headers and trailers are dynamic.

header : name of all table columns (can change anytime)
trailer - number of records

Also i don't want to be dependent on the windows copy commands to go wrong. Is there way within xpcmdshell or t-SQL?

Thanks
0
 
LVL 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 37778053
Use BCP utility only if you have text/ntext or varchar(max) columns but please use SQLCMD utility otherwise and please see switches like -h(headers) at link below:

http://msdn.microsoft.com/en-us/library/ms162773.aspx
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 58
SQL Query - Database name 'TempDB' ignored, referencing object in tempdb - Error 2 79
TSQL recursive CTE challenge... 8 34
sql query help 15 55
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question