BULK INSERT Alternatives

[ShawnJClapper]

Hello all. I'm working on a project that is loading fairly large comma/return character seperates .txt files into a database. I know BULK INSERT is the fastest way to do this, however the company that is the web host for this won't allow me to have BULK INSERT permission so when trying it I get this error:

You do not have permission to use the BULK INSERT statement.

So, for now I'm just reading the file line by line with asp, splitting it by "," , looping and inserting. This takes about 10 to 15 minutes per file which is an annoying long time. The job also has to be run every night as an automated task (haven't really got to this part yet).

My question is, does anyone know some alternatives to using BULK INSERT that do not need special permissions and would be faster than looping through the file?

 

[SQLSister]

In the first place, I would not have my files at a web host that did not give me the permissions I need to do whatever I needed to do to my own database. Have you asked them to change your permissions or to do the task for you?

Can you create a run a DTS task which uses just a regular connection rather than bulk insert?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[ShawnJClapper]

I'm not in control of the web host. It's for a company I'm working with and they are very happy with their hosting company they have been using for years and have many accounts with etc. I have been unable to sway them to another hosting company so I'm stuck with what I have to work with.
I'll look into DTS. Do you know if that can be run from a query in asp or is it something that has to be done via sql manager? I think that might need the same permissions as BULK INSERT does but am not for sure on that...

 

[ShawnJClapper]

Oh and yes we have asked them to change the permissions and they said they don't allow that. Probably because they only have a shared SQL box and not a seperate one for each account and are scared of us stealing too much cpu.

 

[SQLSister]

When you create a dts package you can convert it to vb code. Perhaps that would be the way to go?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief