Bulk Insert \ BCP Alternative

[TLENTINE77]

I apologize for the lengthy explanation and question that follows.

I am searching for an efficient way to load a large amount of data into SQL Server without using BCP or Bulk Insert.

Background:
My application stores data from other applications (not necessarily SQL Server based) and allows users to report against the data.

I have a series of COM objects which are used to parse these other applications and retrieve the appropriate data. My application was originally written so that the "results" of my COM processing would be a delimeted text file that could "uploaded" to SQL Server via Bulk Insert. Once in SQL Server, a number of sprocs would then be invoked to "clean" the data (check for duplicates, etc) and insert it into the appropriate tables.

Problem:
I have a number of clients who do not want to use Bulk Insert since it requires higher level permissions than they are willing to grant to the individual user and it requires the server (the account which the service is running under) to have access to the file on the remote machine. They are also reluctant to have to run the import program themselves each month.

In these situations, I have been forced to populate the SQL Server database one record at a time (from my delimeted file using ADO). As you all can imagine, this can take quite a long time to complete.

Question:
Does anyone know of an alternative to processing this data one record at a time (keeping in mind that I do not have control over windows security, the server running SQL Server, or the user's PC)?

Thanks in advance!

 

[mrdenny]

The three ways to load mass data a DTS, BCP and bulk insert. Anything else will require a custom app to process row by row.

You could write a batch file that splits the csv file and runs a bunch of osql insert commands.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)