Hi guys, here is my question. I am a developer, with a standing knowledge of SQL Server, but I am far from a DBA. Recently a client of mine asked me to write some scripts to allow them to batch update some of their product tables in a SQL Server 2005 database environment. Here's how they want it to work (simplified for the example):
Lets say that have a table filled with product information, we'll call it tblProducts. This table has 10 columns, with an identity column, ProductID, a model number, ModelNumber, a bunch of other data, then finally a Price. Originally, they wanted me to write code that would allow them to view into the database, change some stuff around, save their changes, all via the web (sort of like a content management system). Recently, they changed their minds, and want a download/upload approach to the updating.
What I mean by this is, they want to be able to generate an excel or comma delimited output from tblProducts, listing all the products contained within. The two columns they are most concerned with are the ModelNumber and the Price. Basically, this is to batch change price for a bunch of products via a spreadsheet or comma delimited list. They would go in manually and change the prices to their liking. Finally, they want to FTP up the document (or if there is a way to automate it in .net, that would work too), and batch update the table based on this new spreadsheet or comma delimited list. I would assume this would require writing some sort of DTS package that runs the file.
So my question, in parts, is this :
1) How can I write something that generates an outputted list of all the products in the database, in either excel or comma delimited, that they can download and modify.
2) Once modified, whats the best method to have them upload it to the webserver, or ftp it, and then update the table via this new file (I am a beginner at DTS, so excuse my greenness)
I hear about companies doing this sort of thing all the time, ive just never done it myself. Any help is appreciated. The site that this is all attached to is in ASP.NET/VB.NET, but I think the majority of the grunt work would be on the SQL side.
Thanks!!
Start Free Trial
