SQL Server and VB.Net text file manipulation

[arq3397]

Hi. I am a newbie AS400 operator with a little classroom education in VB.Net and Oracle 9i SQL syntax. I have just been appointed some tasks that require me to manipulate the contents of a text file that will be sent to me every two minutes. I can use SQL Server or windows scheduler to run VB.Net programs as the file is received. However, I am fairly clueless as to where to start.
I need the records from the comma-delimited text file saved to a history table. I also need to update a "Current Activities" table by overwriting some of the records with new data from the text file. I am supposed to delete each record from the text file after I read it so that the file will remain intact and new records may be appended every two minutes.
So ... the bajillion dollar questions ... is there a way for me to delete individual records from a text file using SQL stored procedures? Is there a better way for me to insure that my text file contains only current data?
Any assistance anyone can render would be much appreciated.
: )

 

[pascalsql]

Hmmm, I'm not sure about my answer but as you don't get any yet...

SQL Server is not designed to handle delete in text files, it would break it's own business model ;-)

Assuming the AS400 fills the text file, it probably creates it if it does not exist, under a known name, say AS400file.txt.
With xp_cmdshell, let SQL Server rename this file to AS400file_temp.txt
Import it's content with bulkinsert in your table
With xp_cmdshell, delete AS400file_temp.txt

This way you don't need to delete in the text file, AS400 always starts with a fresh one.


my two €-cents