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.
| Author |
Topic |
|
Einsteinjr
Starting Member
1 Post |
 Posted - 2010-03-07 : 15:03:22
|
| This question has probably been posed many times, but I want to mention a few specific things in my situation.So I'm fairly new to SQL/DB. In fact, I just started working with SQL no more than 1 month ago. So please bare with me :)The situation is this:I'm working on Device/Computer inventory DB. Currently, my company has one very large, flat spreadsheet which has about 30 fields per record (make, model, serial number, RAM, etc.) So far, I've converted this data into SQL by semi-automating the normalization process with some Access + VBA. The result is about 25 tables created, many of those having a 1 to infin relationship with 2 of the tables (tbl_Device and tbl_Device_Computer).The inventory data changes fairly often, BUT my company would still like to use Excel to make updates/additions to the inventory since that is what they are familiar with.My goal is to have some very standardized Excel spreadsheet which users can add data (probably be stored on a share the SQL server has access to), click a button, and viola - updates/additions/deletions are made via a key in the spreadsheet (probably will be serial number and/or unique name).The key is that I want the end user to actually execute the update, but I also don't want them to have to know anything about SQL. The flow would be something like this: 1.) New inventory is purchased by Team X (there are many teams).2.) This team does their inventory in a standardized Excel format3.) Team X sends their spreadsheet to a single person who manages all of our inventory. This person is the one who updates the data to the SQL DB.I've done some research on my own. I'm fairly familiar with VBA, but that just seems like a nightmare to create all of those relationships. For example, if there is a new manufacturer "XYZ" in the spreadsheet that Team X sends, how can I add "XYZ" to my manufacturer table, but also create the relationship in the main Tbl_Device table?Can Integration Services do this? I've been messing around with it some, it seems that there is a bit of a learning curve... It would seem possible to create a package, store it on the server, and have a macro within Excel execute that package remotely.Any help/suggestions would greatly be appreciated!Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-08 : 11:26:21
|
| you do that in Integration services. you can insert the manufacturer to manufacturer table, get generated id value and add it to your destination table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Importing from excel