Member Avatar for 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
...          ...
...          ...
Edited by donjt81_yahoo because: Title corrected
  • 4 Contributors
  • 3 Replies
  • 180 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by codemasters
Member Avatar for 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!

Edited by BitBlt
Member Avatar for 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.

commented: Good solution...no one ever said all the operations HAVE to be inside SQL Server! +8
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.