Post reply

Exporting Data from SQL Server

  • November 5, 2009 at 12:02 pm

    #137060

    We are exporting data from SQL Server using EXEC xp_cmdshell bcp command. Question is if anyone know if you can append to the same file if the file exist out there instead of over writing. Or do you have a better method doing this process. The text will not always be out there because it will processed by other system and remove the the folder. Only time the file will stay if the other system does not process it. This process of exporting the data will be scheduled on SQL Server.

    Thanks for your help and input in this matter. 🙂

  • November 5, 2009 at 12:58 pm

    #1075568

    AFAIK there is no "easy" way of appending to an existing file.

    I remember a few options being discussed a while ago (but I can't find the thread...):

    a) load the content of the file into SQL Server, add the new lines and export it (if original file isn't too large)

    b) use "old fashioned" DOS commands to copy the new file into the old one

    c) store the files always as separate file with a defined name followed by a time stamp (assuming the target app could be modified to handle it...)

    Don't know if that'll help...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 5, 2009 at 1:05 pm

    #1075574

    Thanks. I though of the first but not the second. Another idea I had is use EXEC xp_cmdshell with vbs script that you can append to the file if it is exist. What do you think of this idea? I do not know if our corporation will allow it on the server but so far that is only thought I had.

    Thanks again.

  • November 5, 2009 at 4:14 pm

    #1075643

    Well, that would turn the "old fashioned" DOS command option into a "retrofit version" 🙂 But the basic concept would be the same...

    So, if your company would accept it I think it's the easiest way to go (assuminf your familiar with vbs ...)


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 5, 2009 at 8:29 pm

    #1075687

    Bridget Elise Nelson (11/5/2009)

    Thanks. I though of the first but not the second. Another idea I had is use EXEC xp_cmdshell with vbs script that you can append to the file if it is exist. What do you think of this idea? I do not know if our corporation will allow it on the server but so far that is only thought I had.

    Thanks again.

    Nah... no reason to slow things down with VBS...

    Let's say you have a file that will sit there until your other process picks it up and then deletes it. We'll call that file "File1.txt".

    We'll use xp_CmdShell to BCP data out to another file. We'll call that file "File2.txt".

    What you need to do is create a simple batch file that contains the following commands...

    BCP.exe yada-yada

    COPY File*.txt File1.txt

    DEL File2.txt

    Then, use xp_CmdShell to call the batch file instead of a separate BCP command. File2.txt will very nicely be appended to File1.txt if File1.txt exists. If File1.txt does NOT exist, it will be created.

    Someone hit the "Easy" button, please. 😉

    --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)
    Intro to Tally Tables and Functions

  • November 6, 2009 at 10:28 am

    #1076022

    Button (im)pressed. Nice one, Jeff!

    I thought there was an easy solution out there...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 6, 2009 at 10:33 am

    #1076024

    Thanks. That sounds like a very good idea. I will be trying that.:-)

  • November 6, 2009 at 9:21 pm

    #1076222

    Bridget Elise Nelson (11/6/2009)

    Thanks. That sounds like a very good idea. I will be trying that.:-)

    Thanks for the feedback... If you have the time, please let us know how it all works out for you.

    --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)
    Intro to Tally Tables and Functions

  • November 6, 2009 at 9:24 pm

    #1076224

    lmu92 (11/6/2009)

    Button (im)pressed. Nice one, Jeff!

    I thought there was an easy solution out there...

    Thanks Lutz. I love the "old" ways... so simple... 😀

    --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)
    Intro to Tally Tables and Functions

  • November 6, 2009 at 9:32 pm

    #1076225

    Jeff Moden (11/6/2009)

    Bridget Elise Nelson (11/6/2009)

    Thanks. That sounds like a very good idea. I will be trying that.:-)

    Thanks for the feedback... If you have the time, please let us know how it all works out for you.

    I almost forgot... there's a hidden "feature" to doing this. There will be a special character between the "file sets" that get stored in File1.txt in my example... it's CHAR(26) or {Ctrl-Z} (also known as the EOF character). Most text based processors just ignore it but, if you need to separate the embedded files from each other, just look for the special character.

    Of course, every silver lining is part of a dark cloud... if your text based processor is setup to only load data until it see's the special EOF character, it will only load the first "file set".

    --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)
    Intro to Tally Tables and Functions

  • November 6, 2009 at 9:48 pm

    #1076229

    The natural tool for the job would seem to be SSIS. It's pretty easy too - I just created an SSIS package to export a table from AdventureWorks to a text file (with append) in around two minutes.

    I think I would probably prefer to have all my data import/export routines in SSIS rather than using xp_cmdshell and batch scripts, but things may be different for you.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 12, 2009 at 10:06 pm

    #1078706

    Paul White (11/6/2009)

    The natural tool for the job would seem to be SSIS. It's pretty easy too - I just created an SSIS package to export a table from AdventureWorks to a text file (with append) in around two minutes.

    I think I would probably prefer to have all my data import/export routines in SSIS rather than using xp_cmdshell and batch scripts, but things may be different for you.

    Paul

    How would you do what the OP requested using SSIS? ie. Append it existing file if it exists or create a new file if it doesn't.

    --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)
    Intro to Tally Tables and Functions

  • November 14, 2009 at 4:02 am

    #1079348

    Jeff Moden (11/12/2009)

    How would you do what the OP requested using SSIS? ie. Append it existing file if it exists or create a new file if it doesn't.

    Just use the Flat File Destination component, with the Overwrite property set to false. If the file doesn't exist, it gets created. If the file does exist, it appends! No special trickery required - this time 🙂

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 14, 2009 at 1:49 pm

    #1079403

    Paul White (11/14/2009)

    Jeff Moden (11/12/2009)

    How would you do what the OP requested using SSIS? ie. Append it existing file if it exists or create a new file if it doesn't.

    Just use the Flat File Destination component, with the Overwrite property set to false. If the file doesn't exist, it gets created. If the file does exist, it appends! No special trickery required - this time 🙂

    Thanks, Paul. Heh... sounds like a DOS batch command. 🙂

    --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)
    Intro to Tally Tables and Functions

  • November 15, 2009 at 2:46 am

    #1079441

    Jeff Moden (11/14/2009)

    Thanks, Paul. Heh... sounds like a DOS batch command. 🙂

    Ha. Sure does!

    One of the things I like about working with computers is that there is always a balance between art and science. There are probably twenty or thirty valid ways to approach this sort of problem - SQLCMD, DOS, PowerShell, SSIS, PERL...you name it. I'm sure we could even find a way of controlling the task via the Twitter API if we tried!

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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