Pages

Thursday, May 7, 2015

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:


-- 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
*/

No comments:

Post a Comment