I have a table called Product. In the Product table we have something called ProductCode and NewProductCode. When we first built the tool we were anticipating the NewProductCodes to be coming down the pipe at a later date. So we added this column with NULL. So right now the DB looks like this.
Now the NewProductCodes are being sent down to us to be added to this table. I am trying to figure out an efficient way to update my Product table (About 10,000 records) with this new information. I can make it work in some ugly ways but I would like to see an efficient way to do it. Any ideas.
The mapping of old codes to new codes has been sent down to me in an excel format like this. I can always add this to a temp table to do the update and then delete that temp table.
If it's Excel and it's a one time thing - or even if it's re-occuring rarely - then I'd concatenate the values into SQL statements in Excel and copy paste them in SQL. It can either be the update statement itself or it can be an insert statement for your temp/working table.
You should end up with a formula in Excel like this ="update product set newproductcode = '" & A2 & "' where productcode = '" & B2 & "'". Fill it down to all your rows and copy/paste it to SQL. it's faster than all the alternatives.