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 export data directly into a flat file?

Execute the following Microsoft SQL Server Transact-SQL script in Query Editor to create a stored procedure which exports directly to a flat file using nested cursors and command shell ECHO:

WARNING: xp_cmdshell & SQL Server security

USE AdventureWorks;

GO

-- SQL Server stored procedure - SQL export to flat file - SQL nested cursors

CREATE PROC SupplierProductSummary

           @FilePath VARCHAR(100)

AS

  BEGIN

    DECLARE  @Line NVARCHAR(1000)

    DECLARE  @Command NVARCHAR(2000)

    DECLARE  @Return INT

    DECLARE  @VendorID   INT,

             @VendorName NVARCHAR(50)

    DECLARE  @ProductName NVARCHAR(50)

    

    SET @Line = '******** SUPPLIERS PRODUCTS REPORT ********'

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

    

    EXEC @Return = MASTER..xp_cmdshell

      @Command ,

      no_output

    

    DECLARE curVendor CURSOR  FOR

    SELECT   VendorID,

             Name

    FROM     Purchasing.Vendor

    WHERE    CreditRating = 1

    ORDER BY VendorID

    

    OPEN curVendor

    

    FETCH NEXT FROM curVendor

    INTO @VendorID,

         @VendorName

    

    WHILE @@FETCH_STATUS = 0

      BEGIN

        SET @Line = '. '

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

        

        EXEC @Return = MASTER..xp_cmdshell

          @Command ,

          no_output

         

        SET @Line = '***** Products From Supplier: ' + @VendorName

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

        

        EXEC @Return = MASTER..xp_cmdshell

          @Command ,

          no_output

        

        DECLARE curProduct CURSOR  FOR

        SELECT v.Name

        FROM   Purchasing.ProductVendor pv,

               Production.Product v

        WHERE  pv.ProductID = v.ProductID

               AND pv.VendorID = @VendorID

        

        OPEN curProduct

        FETCH NEXT FROM curProduct

        INTO @ProductName

        

        IF @@FETCH_STATUS <> 0

          BEGIN

            SET @Line = '*** NO PRODUCTS AVAILABLE AT THIS TIME *** '

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

            

            EXEC @Return = MASTER..xp_cmdshell

              @Command ,

              no_output

          END

        

        WHILE @@FETCH_STATUS = 0

          BEGIN

            SET @Line = ' ' + @ProductName

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

            

            EXEC @Return = MASTER..xp_cmdshell

              @Command ,

              no_output

            

            FETCH NEXT FROM curProduct

            INTO @ProductName

          END

        CLOSE curProduct

        DEALLOCATE curProduct

        

        FETCH NEXT FROM curVendor

        INTO @VendorID,

             @VendorName

      END

    

    CLOSE curVendor

    DEALLOCATE curVendor

  END

GO

 

-- SQL Server execute stored procedure

EXEC SupplierProductSummary   'F:\data\export\SUPPreport4.txt'

GO

 

/* Partial results in flat file

 

******** SUPPLIERS PRODUCTS REPORT ********

***** Products From Supplier: International

 Lower Head Race

 LL Road Rim

 ML Road Rim

 HL Road Rim

***** Products From Supplier: Premier Sport, Inc.

 Touring Rim

*/

 

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