Exporting Data from SQL Server

Question

Post reply

Exporting Data from SQL Server

Bridget Elise Nelson

SSC-Addicted

Points: 492
More actions
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. 🙂

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

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]

Bridget Elise Nelson SSC-Addicted Points: 492 More actions · Answer 2

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.

LutzM SSC Guru Points: 107049 More actions · Answer 3

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]

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 4

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)

LutzM SSC Guru Points: 107049 More actions · Answer 5

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]

Bridget Elise Nelson SSC-Addicted Points: 492 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 8

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)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 9

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)

Paul White SSC Guru Points: 150467 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

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)

Paul White SSC Guru Points: 150467 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 13

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)

Paul White SSC Guru Points: 150467 More actions · Answer 14

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