Sunday, February 1, 2009

Do the bulk updates using Microsoft SQL Server Correctly.

This is the scenario:
You have a table called tblItem with fields,
itemID, itemTitle, itemDescription, itemImage and itemPrice.
itemID is set as the primary key or it is the field that can be used to uniquely identify a given row.
And this table is populated with 1000 records.
Imaging that you have to change the price of about 700 items.
And your data entry team supplied you a Microsoft Excel spreadsheet with itemID and new itemPrice.
So you have to update the tblItem table, without effecting other 300 items.

Before you start updating:
Make sure that itemID is unique in tblItems and the spreadsheet, otherwise you will get an error.

How to do the update:
  • Import data from the spreadsheet and create a new table called tblTemp
  • The fields will be tempID and tempPrice
  • Run this query:
UPDATE tblItem
SET itemPrice = (SELECT tempPrice FROM tblTemp WHERE tempID = itemID)
WHERE itemID IN (SELECT tempID FROM tblTemp);

With this query you can make sure that other records which aren't present in the spreadsheet will not get updated and only the records present in the spread sheet get update.

Words of caution:
  • Don't forget to delete the temporary table.
  • It is always a good idea to make backup.