Solved

Xp_cmdshell/bcp - How to add header & trailer records

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

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

30 Experts available now in Live!

Get 1:1 Help Now