Advice on most efficent approach please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Advice on most efficent approach please

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2008-07-18 : 06:23:51

I have a very small sql server 2000 database - only 2 tables. One of these tables contains data which I want the users to be able to overwrite on an occasional basis from a web page using asp.net 1.1. The SQL Server box is a different one to the IIS box

Users will have a file in CSV format which will always have a known, fixed set of columns. This file will have no more than about 70,000 records.

So when they do an update I need to clear out the existing table (truncate) and then insert the new data from the CSV file and recreate the clustered index

What I am unsure of is the best approach in this sort of scenario.

The options I have come up with are

1) Convert the csv file to a dataset and then update the database
2) Use bcp (which I am not particularly familiar with) or DTS (which I am familiar with
3) ?

I quite like the idea of option 1 though I suspect it will be a rather inefficient method.

If I use DTS (or bcp) I am going to have to work out how to access the file from the DTS package which sounds messy. I should be able to get it to the IIS box but I am not sure SQL Server can access it from there and I have little access to it

Does anyone have any thoughts or suggestion?

thanks in advance

steve

-----------

ASCII and ye shall receive.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-18 : 09:16:39

for this task i'm not really excited about 2.
i'd load the file at the client and create N batches of insert into () select ... union all ... from it and then insert that
using SqlCommand.ExecuteNonQuery. that way you can have a progress bar and everything.

or am i missing the business requirement?
as i understand it you want a person to insert that file from it's disk to your server over the web.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-07-20 : 18:43:42

Check out the "SqlBulkCopy" (which can be used to perform massive data copy operations between different sources)

Unfortunately its available in version 2.0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-20 : 19:03:32

For DTS or bcp, the file can be on the client machine/web server. It's only from a job, that it needs to be on the database server. But I'm not excited about option 2 either.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2008-07-21 : 05:04:40

Many thanks for the replies, they are very helpful.

The idea behind getting the file to IIS was to have it in a known location but when I look at it now I realise that is probably flawed logic.

Spirit I will take a good look at your suggestion as I think it will resolve this for me.

Afrika - sadly at the moment this has to be .net 1.1

Steve

-----------

ASCII and ye shall receive.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources