Avatar of KevinDriedger

asked on 

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

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?
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Avatar of appari
Flag of India image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of KevinDriedger


Yeah--that's good.  I needed to make a few minor changes, but this basically gives me what I'm after.

Thank you.
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo