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 |
|
talford
Starting Member
3 Posts |
 Posted - 2011-01-26 : 11:03:28
|
| I've become completely confused myself here with this, and am not even sure how to ask the question, but hopefully someone can help!I have a table with several fields, two of them being item and month, both integers. An item can be superseded by another item. When it is superseded, i would like to INSERT records into the table where item and month combinations exist for the old item, but don't exist for the new. This is to allow me to copy old item data over to the new item, but retain historical records.For example, the table contains:ItemA 1ItemA 2ItemB 1ItemC 3ItemC has replaced ItemA so i want to INSERT two ItemC lines for months 1 and 2, but am having problems working out how to do this.I was hoping to just be able to update the table to make all ItemA = ItemC but this will not give us the history\audit we need.Hopefully this makes sense.Seems like it should be simple but i can't work it out, so any help would be much appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-26 : 11:07:58
|
| what according to you designates replacement? how do you came to conclusion that ItemC replace A? what all values you consider for selecting an Item as replacement of other?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-26 : 11:10:32
|
This?insert your_table(Item,month)select 'ItemC', -- as hard coded value, you know?month from your_table where Item='ItemA'You can execute the SELECT part without insert to see the rows that would be inserted...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
talford
Starting Member
3 Posts |
Posted - 2011-01-27 : 05:39:36
|
| Thanks for the replies.visakh16 - this is for a sales system that records forecasts, actual sales, budget etc. We may put forecasts into the system using a temporary item number. For example, the table i'm talking about actually holds the item, month, forecast details, actual sales details etc. When the item is created for real in our MRP system it will be given its final item number, which will be different to the temporary item number. At that point we need to recreate all the temp item records for the new item. Just changing the item number from old to new would resolve this issue, but we would then lose the history. I suppose one option is to create a history table and copy data out to that and then rename the old items to new, but i could end up with duplicated item, month combinations...which leads me on to....webfred - that's something i've already tried (one of many) i'm afraid. Unfortunately if an (ItemA, month) record exists and there is already a matching (ItemC, month) then this SQL statement causes a second, duplicated (ItemC, month) record to appear. But it's almost there, so i guess there's just something else to add, but i'm having trouble working it out for some reason.Thanks for your help so far. |
|
|
|
talford
Starting Member
3 Posts |
Posted - 2011-01-27 : 05:48:38
|
| Typical!! Post a reply then 2 minutes later i work it out.webfred - you got me on the right track (i think by actually simplifying it). Got it sorted out using:insert table(Item,sales_month) select 'itemC',sales_month from table where Item='itemA' AND sales_month NOT IN (SELECT sales_month FROM table WHERE item = 'itemC') |
|
|
|
|
|
|
|
|
INSERT from table into same table