All

I have, in my VFP Desktop application, a utility that allows the user to specify criteria for data she wishes to retrieve from eight Foxpro 2.6 free tables that will be uploaded to a website for updating a SQL server database available for viewing by her clients.

My utility creates TXT Files, one for each table of the requested data. The fields are delimited with " | " (that's the pipe character), and the text is wrapped in underscores. Using these delimiters is based on the type of data I'm dealing with and the fact that I was preparing this data for importing through the BULK INSERT command. Once the files are created, my utility automatically uploads the files via FTP to a designated folder on the site. That's as far as I've gotten.

My intention is to write a stored procedure to automatically BULK INSERT the data into my "STAGING TABLES" anytime there are proper TXT files present in the designated location. Then another stored procedure or group of procedures will "massage the data" in preparation for the INSERT INTO command to transfer the data into properly normalized tables in the same database. Initially, each customer's import will be larger, (could average between 200 and 18000 records, not on all tables, but on some. After a client's initial import, updating will be minimal.

This website will be updated on a daily basis with new and modified records. My questions are, is BULK INSERT the way I want to go for getting the data into my staging tables? Is there something different? Do I need to export from VFP in some other format?

What type of script or "task scheduler" do I use to automate the process and alert SQL Server to the presence of TXT files to be imported. Remember this database will be located on my host provider's SQL Server instance.

Thank you