Appending to file with BCP
I found an interesting tip about BCP (the SQL Server command line tool) on the MSDN Forums
While exporting data from SQL Server to a file using BCP, it always overwrites the contents of the file specified in command if it already exists or creates a new file but it cannot append.
If ever you need the output of a query appended to the info in a existing data file, use an intermediary file to manage the updation of the data file -
Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SYourServerName'
Exec master..xp_cmdshell 'type "c:\tempexportfile.txt" >> "c:\mainexportfile.txt"
Also see:
Exporting to Excel with bcp
While exporting data from SQL Server to a file using BCP, it always overwrites the contents of the file specified in command if it already exists or creates a new file but it cannot append.
If ever you need the output of a query appended to the info in a existing data file, use an intermediary file to manage the updation of the data file -
Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SYourServerName'
Exec master..xp_cmdshell 'type "c:\tempexportfile.txt" >> "c:\mainexportfile.txt"
Also see:
Exporting to Excel with bcp
Excellent! Thanks, you saved my day!
ReplyDeleteMy result set doesn't have a header line (but it does when I use it natively). is there a way to turn on headings?
ReplyDeleteExcept this doesn't work with unicode...
ReplyDelete