Updating multiple records in a table

[donjt81_yahoo]

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.

ProductCode  NewProductCode  ... ... ...
154          NULL
648          NULL
467          NULL
788          NULL
165          NULL
546          NULL
...          NULL
...          NULL

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.

ProductCode  NewProductCode
154          r_sc29
648          r_sc52
467          r_sc79
788          r_sc15
165          r_sc66
546          r_sc45
...          ...
...          ...

[BitBlt]

Your best bet is to use SSIS to import the spreadsheet into a work table (not a temp table!) and execute the update join from there as an "Execute SQL Task". Maybe something like this:

update a
set a.newproductcode = b.newproductcode
from dbo.product a
inner join dbo.wkProduct b
on a.ProductCode = b.ProductCode

Granted, it's not super efficient if it's a one-time update. But if it is to be periodic, you can schedule the thing as long as your spreadsheet format doesn't change.

It sounds like that's pretty much what you're already doing though. If you do wind up scheduling it, just be sure to truncate your work table at the start of the Control Flow!

[adam_k]

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.

[codemasters]

SSIS (Sql Server Integration Services) provides you a feature to map the excel sheet and the database.
The following link shows how to perform it and I think it has a great efficiency.
You just need to open the connections and map the excel sheet to your database destination.
http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205