recently i've been working on querying text files from sql. there is a little time delay but it works. i need to know if there is any possibility to insert and update the data stored in the flat text file
I've never worked with text files in there given state, I would BCP or BULK INSERT the data into SQL Server, manipulate it there then write it back out. This way is much faster as well. which I found out on my own because I did try to do it using Linked Servers.
Here is what I did, which is probable the steps that you took, but may be new to some other people.
1) Created a test file where the data was tab delimited, placed it in C:\Temp
2) Created a scheme.ini file and placed it in c:\temp as well, (see KB Q155512)
3) used sp_AddLinkedServer , using Jet 4.0 as the provider, see the example in BOL for sp_AddLinkedServer at the bottom of the page
Once I created the linked server I could read the data (SELECT) and I was able to write (INSERT) to the file as well, but could not UPDATE. I would get error 7333, the Provider does not support bookmarks. The workaround for me was to go back to my original plan, BULK INSERT into a table and manipulate the data in a SQL table.
In conclusion INSERT were possible but not UPDATEs, maybe if a different OLE DB Provider is used with sp_AddLinkedServer ?