Execute the following Microsoft SQL Server T-SQL scripts to create a stored procedure for sample flat file export of data from the Production.Document table, execute it and demo data export using bcp with xp_cmdshell:

WARNING: xp_cmdshell & SQL Server security

-- bcp export query result to flat file - QUICK SYNTAX

EXEC xp_cmdshell 'bcp "select ProductID, Name from AdventureWorks2008.Production.Product" queryout "C:\data\export\products.txt" -T -SDELLSTAR\SQL2008 -c -t,'

------------

USE AdventureWorks;

GO

-- Export data from SQL Server to a flat file with xp_cmdshell and ECHO
-- SQL Server stored procedure create 

CREATE PROCEDURE sprocExportDocumentTitles @Path varchar(128)

AS

BEGIN

DECLARE @Line varchar(1024)

DECLARE @Command varchar(2048)

DECLARE @i int, @imax int, @Return int

SELECT @imax=MAX(DocumentID) from Production.Document

SET @i = 1

WHILE (@i <= @imax)

BEGIN

      SELECT @Line=Title from Production.Document

            WHERE DocumentID = @i

      SET @Command= 'echo ' + @Line + ' >>'+ @Path

      EXEC @Return = master..xp_cmdshell @Command , no_output -- sql server xp_cmdshell

      SET @i = @i+1

END -- WHILE

END -- CREATE PROC

GO

-- EXECUTE STORED PROCEDURE 

EXEC sprocExportDocumentTitles 'F:\data\export\SampleDocTitle.txt'

GO

------------

-- SQL Server exporting data to text file with bcp

EXEC xp_cmdshell 'bcp "select ProductID, Name from AdventureWorks2008.Production.Product" queryout "C:\data\export\products.txt" -T -SDELLSTAR\SQL2008 -c -t,'

/*

output

NULL

Starting copy...

NULL

504 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 15     Average : (33600.00 rows per sec.)

NULL

*/

RELATED ARTICLES:

http://www.sqlusa.com/bestpractices2005/notepad/

Generating CSV Files - Using BCP