Mass Insert

Hello,

This is my scenario: My database is sitting on Server A, My website is sitting on Server B. And I have a file (a csv file) in the user's computer that I want to use to insert data into a table in Server A. I can upload the file to Server B. What is the easiest (but not a performance drag) way to accomplish this?
The server is a SQL Server 2000, and I the website is running on the .Net 2.0 framework.

I do have some ideas, but I don't know how bad the performance will be:

I was thinking of doing a bulk insert, but since these are two different servers, I ruled that out.I can have a insert statement in a loop and keep executing the insert statement.

I have another question too, will there be better performance, if I put the insert statement in a stored procedure, and keep calling that stored procedure instead of writing inline sql?

Thanks in advance for your reply.

You didn't mention how large the data set is that you are loading, or how frequently you will need to load this data. That would certainly have an impact on which course of action to take.

You have a couple of different options. You mentioned bulk insert, which is a good solution for quickly loading a large amount of data. You can certainly do a looped INSERT statement, but if you are loading many thousands or millions of records, performance for this option may be very poor. Depending on the nature of the query, calling a stored procedure instead of straight SQL may yield you a little bit of a performance gain, but it probably won't make a huge difference.

Another option would be using the bcp utility to load the data. Your best bet with this is to get the import file on the database server and run from there.

I hope this helps. Feel free to post more detailed information about your environment and what you are trying to accomplish, and I'll take another look.

Hi Tim,

Thanks for your reply. The amount of data I am trying to upload will be about 1000 records (with about 20 columns). I also have to run checks to see if the data already exists. If using bulk insert I planned to use bulk insert get it to a temporary table and then use a store procedure to check for already existing data and then add the new ones in.

This data will be uploaded once a month, if that helps. There will be daily additions of may be upto 10 records for which I plan to use form view or something of that sort.

Please let me know of any other ideas you might have. Thanks.

If you're only loading the data once per month, can I presume that this is a maintenance task rather than a user-interactive task? If this is the case, you might be better to use a DTS package to transform and load your data. In fact, if your source .csv file is consistent you could set this up to run automatically at a given date and time each month.

On the other hand, if you need for the user to be able to use your web app to load this data, you could use an iterating INSERT statement to load the data one row at a time. Loading 1000 records should not take a huge amount of time (in theory anyway - your mileage may vary), and since it's a monthly process it could be done during non-peak usage times.

As to doing a conditional insert, check into an INSTEAD OF trigger for your logic:http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

Hope this helps.