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 |
|
madwoman
Starting Member
4 Posts |
 Posted - 2009-01-25 : 10:59:49
|
| I am totally new to MSSQL and have a database associated with a website that I inherited and which I have been given MyLittleAdmin to manage the database. The data in the tables is associated with a product catalogue that is operating an online shop. My problem is that I need to update all the prices in the dbo.products table and then switchover to the new prices as of 1st Feb. This means that I need to take a copy of the dbo.products table update it offline and then load the amended version back into the live systems on 1st Feb.Can anyone give me simple steps as to how I can do this in MyLittleAdmin - or indeed in another admin tool if there is a better one available. I have exported a backup file (.bak) which I assumed I could edit and then upload and restore but I can't see how I can edit the .bak file offline |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-25 : 12:12:32
|
| why do you want to do like this? isnt this just a matter of adding two datefields validfrom and validto to table. just add a new record with new price with each item with validfrom as feb 1 and validto as NULL. for existingones set validfrom as 1 jan 1900 and validto as NULL. Always look for this range while taking price to get price applied at that time. |
 |
|
|
madwoman
Starting Member
4 Posts |
Posted - 2009-01-25 : 14:49:47
|
| Doesn't this approach mean adding a whole new set of records for all my products (ie. several hundred)and also amending the website scripts that call the products into the display pages. That sounds like a lot more work than the way I was planning to do it especially if I have to repeat the process in terms of adding a new record set every time the product prices are updated. Or am I misunderstanding what you mean? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-26 : 08:21:19
|
| you need to add new records only for cases where prices have changed. this is a scalable solution as whenever the price changes all need is to add a new record and close down current active record by setting validto as date of change |
|
|
|
madwoman
Starting Member
4 Posts |
Posted - 2009-01-26 : 08:39:43
|
| sorry but that means over 500 new records to be added with new prices and that is a lot more work that trying to copy the existing records update them and then restoring the amended backup....plus to pick up the validfrom value in the shopping basket then I have to amend all the product selection scripts. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-26 : 08:50:49
|
| ok...then what happens when you need to do some retro data analysis at a later point? will you have any evidence of what price was during analysing period at all with this? |
|
|
|
madwoman
Starting Member
4 Posts |
Posted - 2009-01-26 : 10:48:15
|
| my client doesn't need to track changes in prices retrospectively - he just sells his product and takes the money and only really analyses product group turnover numbers as the percentage markup that he charges is fixed so timing of previous price changes is irrelevant. |
|
|
|
|
|
|
build new data set whilst existing data stays live