troubleshooting Question

Need more efficient sql code to update a table from another table.

Avatar of KevinDriedger
KevinDriedger asked on
Microsoft SQL Server 2005
2 Comments1 Solution179 ViewsLast Modified:
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.  

Any ideas?

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros