Post reply

Export SQL Database to flat file with header(column) name

  • June 24, 2008 at 6:56 am

    #381305

    hi,

    i have tried bcp, it can dump only table data from sql to txt file- i need column name too. Is their any way to get column name as first raw with data

    Declare @str varchar(500)

    set @str='bcp "SELECT * FROM MYDB..MyTable" queryout c:\datasources\MYTable_' +REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') +'.txt -Sservername -c -U******* -P******* -T /t "|"'

    print @str

    exec master..xp_cmdshell @str

    GO

    this statement can create text file with date and time stamp and export from sql to txt file but couldn't load column name in first raw

    please help me out

    Thanks

  • June 26, 2008 at 3:48 pm

    #835012

    Play with isql utility.

    It has full description in BOL.

    Hopefully it's what you need.

    _____________
    Code for TallyGenerator

  • June 26, 2008 at 6:45 pm

    #835068

    OSQL utility might be better... it will do everything that SQL Server will do... ISQL doesn't have all the features (if I remember correctly).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 28, 2008 at 1:10 am

    #835728

    thanks jeff

  • June 28, 2008 at 10:28 am

    #835779

    rbamania (6/28/2008)

    thanks jeff

    Thanks, but Sergiy was first to set you on the right track.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 5, 2008 at 1:16 pm

    #838544

    hi Sergiy,

    thanks for your help

    Regards

  • March 17, 2010 at 12:41 pm

    #1135489

    Hi,

    If memeory serves correctly, the answer is no with bcp.

    I built a process for a customer that exported transactions every day and ftp'd the data up to an automated process.

    I ended up building a template with just the header file and then I would do a double pass on the export; fist exporting to a temporary file and then appending that to the header file.

    It's been running every since.

    I shudder to think what it will have to look like when the customer upgrades to SQL2005.

  • September 29, 2010 at 3:59 am

    #1228849

    rbamania (6/24/2008)

    hi,

    i have tried bcp, it can dump only table data from sql to txt file- i need column name too. Is their any way to get column name as first raw with data

    Declare @str varchar(500)

    set @str='bcp "SELECT * FROM MYDB..MyTable" queryout c:\datasources\MYTable_' +REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') +'.txt -Sservername -c -U******* -P******* -T /t "|"'

    print @str

    exec master..xp_cmdshell @str

    GO

    this statement can create text file with date and time stamp and export from sql to txt file but couldn't load column name in first raw

    please help me out

    Thanks

  • September 30, 2010 at 9:48 pm

    #1230011

    kailash.vicky2004 (9/29/2010)

    rbamania (6/24/2008)

    hi,

    i have tried bcp, it can dump only table data from sql to txt file- i need column name too. Is their any way to get column name as first raw with data

    Declare @str varchar(500)

    set @str='bcp "SELECT * FROM MYDB..MyTable" queryout c:\datasources\MYTable_' +REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') +'.txt -Sservername -c -U******* -P******* -T /t "|"'

    print @str

    exec master..xp_cmdshell @str

    GO

    this statement can create text file with date and time stamp and export from sql to txt file but couldn't load column name in first raw

    please help me out

    Thanks

    My recommendation is to post this as a new thread... more people will likely see a new thread.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply