Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    19

    Post Unanswered: Export Data as text format

    Hi All,

    How to export table data as text format with column name from sql server 2008

    Advance Thanks
    G Arunagiri

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's my favourite method: bcp Utility
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    An example for including the headers. This is dynamic - a less complicated (but more labourious) approach is to use hard coded views.
    Export Query results to Excel with Column names in T-SQL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2010
    Posts
    19

    export the data from sql server 2008

    Dear All,

    USE [CHURN1]
    GO
    /****** Object: StoredProcedure [dbo].[ExportData_In_TextFile] Script Date: 07/22/2010 11:21:56 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /************************************************** ************************************************** **
    Name : G Arunagiri
    Product : SQL SERVER
    File Name : ExportData_In_TextFile.sql
    Date written : 13/10/2009
    ************************************************** ************************************************** ***/
    ALTER PROCEDURE [dbo].[ExportData_In_TextFile]
    @DBName Varchar(255), --To Get Database Name
    @TableName Varchar(255), --To Get Table Name
    @FilePath VARCHAR(1000), --To Get Valid FilePath
    @Separator Varchar(1), --To Get Field Separator
    @Result INT = 0 OUTPUT,
    @ErrDesc VARCHAR(500) OUTPUT

    AS

    DECLARE
    @Query VARCHAR(8000),
    @BCPCmd Varchar(8000)


    SET NOCOUNT ON

    BEGIN TRY

    IF @DBName='' OR @DBName IS NULL
    Begin
    SET @Result = -1
    SET @ErrDesc = 'Database Name can not be null'
    RETURN
    End

    IF @TableName='' OR @TableName IS NULL
    Begin
    SET @Result = -1
    SET @ErrDesc = 'Table Name can not be null'
    RETURN
    End

    IF @FilePath='' OR @FilePath IS NULL
    Begin
    SET @Result = -1
    SET @ErrDesc = 'File Path can not be null'
    RETURN
    End


    IF @Separator IS Null
    SET @Separator=','


    SET @Query='SELECT * From ' + @DBName + '.dbo.' + @TableName

    SET @BCPCmd='bcp "' + @Query + '" queryout ' + @FilePath + ' -T -c -t "' + @Separator + '"'
    EXEC master..xp_cmdshell @BCPCmd
    END TRY
    BEGIN CATCH

    SET @Result = Error_Number()
    SET @ErrDesc = Error_Message()
    RETURN
    END CATCH
    SET NOCOUNT OFF
    SET @Result = 0
    RETURN 0




    Thanks
    G Arunagiri

  5. #5
    Join Date
    Jul 2010
    Posts
    19
    how to include the column name in the output.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please see post #3
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •