Simple way to use BCP to read and write from a SPROC

What 开发者_如何学JAVAis the simplest way to read and write files in a sproc using BCP?

For reading a file in T-SQL, I recommend using the BULK INSERT statement instead of messing with bcp. They use the same underlying mechanism, but if you're in a T-SQL stored proc already, it's best not to hop out into command line land.

Unfortunately, as far as I know, there's no SQL statement you can execute to write a file. So you'll have to resort to doing something like exec master..xp_cmdshell @cmd. You can use bcp or osql as your command. If you use bcp, this page has a nice tutorial, but just to summarize, here's some sample code:

-- make a pipe delimited file... requires access to xp_cmdshell and the file system
declare @cmd varchar(8000)
select @cmd = 'bcp mydb.dbo.tblWhatever out "c:\bcp\tblWhatever.txt" -c –t| -T -S' + @@servername
exec master..xp_cmdshell @cmd