Solved

Xp_cmdshell/bcp - How to add header & trailer records

Posted on 2012-03-28
3
1,749 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
Comment Utility
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
Comment Utility
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 39

Accepted Solution

by:
lcohan earned 250 total points
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now