Xp_cmdshell/bcp - How to add header & trailer records

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
crazywolf2010Asked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
KeldenConnect With a Mentor Commented:
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
 
crazywolf2010Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.