Update Existing Records and Insert New Records into one table with values from another table
Posted on 2005-04-27
I have a source table (iMaster) and a destination table (tblPartDetail). This query will be set up to run as a nightly DTS package. It's goal is to UPDATE the tblPartDetail table with data from the iMaster table IF the record already exists in the tblPartDetail table. If a matching record doesn't exist, the process should INSERT the new record into the tblPartDetail table with the values from the iMaster table.
In addition, the design of the table doesn't allow an identity insert because we have other web-based insert processes that already manage that value, so i wanted to use the SELECT MAX to get the existing high ID value.
The UPDATE portion of the statement works fine by itself. But what i want to know is how to call the VALUES for the insert statement from the iMaster table (denoted by the ??? in the end of the statement). I thought i could call the column names (iM.longDesc), but Query Analyzer says that's wrong.
And will this statement, when running correctly, automatically loop through all the new inserts until it's EOF?
CURRENT STATEMENT FROM QA
-- update existing records in tPD
SET tPD.partName = iM.description, tPD.unitOfMeasure = iM.unit, tPD.engineTypeID = iM.deptID, tPD.partType = iM.sellUp, tPD.shortDescription = iM.longDesc, tPD.price = iM.priceA, tPD.netWeight = iM.netWeight, tPD.displayStatus = iM.popCode
FROM tblPartDetail tPD, iMaster iM
WHERE tPD.hpPartID = iM.partID
-- locate records in iM that do not exist in tPD
IF NOT EXISTS(SELECT *
WHERE (tblPartDetail.hpPartID = iMaster.partID))
-- Get high ID value for new record insertion
SELECT MAX(partID)AS nextPartNumber
-- Insert records which do not exist in tPD into iM
INSERT INTO tblPartDetail
VALUES (('nextPartNumber'+1, ???))