DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a flat file from a stored procedure?

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE