Solved

Xp_cmdshell/bcp - How to add header & trailer records

Posted on 2012-03-28
3
1,855 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

690 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