I have four tables as follows:
Locations (LocID int, LocationName nchar(20))
Products(ProductID int, ProductName nchar(20))
Inventory(LocID int, ProductID int, Quantity int) --> PK on locID and ProdID, with FK to previous 2 tables
InvUpdates(LocationName nchar(20), ProductName nchar(20), Quantity int)
I need an efficient way of transferring information from InvUpdates into the Inventory table with the following constraints:
a) if InvUpdates.LocationName doesn't exist in the Locations.LocationName, create a new Locations record
b) if InvUpdates.ProductName name doesn't exist in Products.ProductName ignore that invUpdates record
c) update Inventory records if they already exist with the given locID/ProdID combination, else insert a new Inventory record.
Procedurally I think I need to
a) get the LocID from Locations using the LocationName (and create a new record if necessary)
b) get the ProdID from Products using the ProductName
c) if I got a valid ProdID, check if there is already an Inventory record with the given LocID/ProdID combination. If so, update it, else insert a new one.
I am so steeped in procedural programming that I don't know how to write a SP to do this without a cursor. I assume there is a better way, but it's beyond me.