Update Existing Records and Insert New Records into one table with values from another table

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
UPDATE    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 *
      FROM tblPartDetail
      WHERE (tblPartDetail.hpPartID = iMaster.partID))

-- Get high ID value for new record insertion
SELECT MAX(partID)AS nextPartNumber
FROM tblPartDetail

-- Insert records which do not exist in tPD into iM
INSERT INTO tblPartDetail
(tPD.partID,tPD.hpPartID,tPD.hePartNumber,tPD.engineTypeID,tPD.partType,tPD.partName,tPD.unitOfMeasure,tPD.shortDescription,tPD.price,tPD.netWeight,tPD.displayStatus)
VALUES (('nextPartNumber'+1, ???))
mshughesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dbeneitConnect With a Mentor Commented:
Good evening,

The error apears because the query of "if exists" must include iMaster table.
You can only use fields of the from clause.
IF NOT EXIST and IF EXIST should be use to decide a
 
-- locate records in iM that do not exist in tPD
IF NOT EXISTS(SELECT *
          FROM tblPartDetail
          WHERE (tblPartDetail.hpPartID = iMaster.partID))


anyway you can test:
-- locate records in iM that do not exist in tPD
IF NOT EXISTS(SELECT *
          FROM iMasterleft join tblPartDetail  on tblPartDetail.hpPartID = iMaster.partID
          WHERE tblPartDetail.hpPartID  is null)
I want to locate the imasters records that if i link them by id, they haven't a equivalent tblPartDetail record



0
 
dbeneitCommented:
IF NOT EXISTS(SELECT *
     FROM tblPartDetail
     WHERE (tblPartDetail.hpPartID = iMaster.partID))
you can use a iMAster Field, the befor statment always return true


-- Get high ID value for new record insertion



SELECT MAX(partID)AS nextPartNumber
FROM tblPartDetail

-- Insert records which do not exist in tPD into iM
INSERT INTO tblPartDetail
(partID,hpPartID,hePartNumber,engineTypeID, partType,partName,unitOfMeasure,shortDescription,price,netWeight,displayStatus)
SELECT 'nextPartNumber'+1,/* all the fields from IM in order */ ...., iM.unit, iM.description, iM.deptID, iM.sellUp,  iM.longDesc, iM.priceA, iM.netWeight, iM.popCode
FROM iMaster iM left join  tblPartDetail tPD ON (tPD.hpPartID = iM.partID)
WHERE tPD.hpPartID IS NULL

In this way you always the same 'nextPartNumer', but you can  do it with another way

SELECT 'nextPartNumber'+count(tpd2.partid) ,/* all the fields from IM in order */ ...., iM.unit, iM.description, iM.deptID, iM.sellUp,  iM.longDesc, iM.priceA, iM.netWeight, iM.popCode
FROM iMaster iM left join  tblPartDetail tPD ON (tPD.hpPartID = iM.partID) left join tblPartDetail tPD2 on (tPD2.hpPartID = iM.partID and tPD.hpPartID  <=tpd2.partID)  
WHERE tPD.hpPartID IS NULL  
group by iM.unit, iM.description, iM.deptID, iM.sellUp,  iM.longDesc, iM.priceA, iM.netWeight, iM.popCode,tPD.hpPartID
0
 
mshughesAuthor Commented:
dbeneit,

Thanks for the reply.  I wasn't quite sure what you were saying on the IF NOT EXISTS stmt.  Based on what you've provided, i have a finalized statement that reads as below.  When i parse the query in QA, it's ok, but when i try to execute the query, it responds with:

Server: Msg 107, Level 16, State 3, Line 8
The column prefix 'iMaster' does not match with a table name or alias name used in the query.

This error seems to be referencing the IF NOT EXISTS stmt, but i don't know why.  There error is misleading because iMaster is the table name.  Any thoughts?


-- update existing records in tPD
UPDATE    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 *
            FROM tblPartDetail
            WHERE (tblPartDetail.hpPartID = iMaster.partID))

-- Get high ID value for new record insertion
SELECT MAX(partID)AS nextPartNumber
FROM tblPartDetail

-- Insert records which do not exist in tPD into iM
INSERT INTO tblPartDetail
(partID,hpPartID,hePartNumber,engineTypeID, partType,partName,unitOfMeasure,shortDescription,price,netWeight,displayStatus)
SELECT 'nextPartNumber'+count(tpd2.partid),iM.partID, iM.partNumber, iM.deptID, iM.sellUp, iM.description, iM.unit, iM.longDesc, iM.priceA, iM.netWeight, iM.popCode
FROM iMaster iM LEFT JOIN  tblPartDetail tPD ON (tPD.hpPartID = iM.partID) LEFT JOIN tblPartDetail tPD2 ON (tPD2.hpPartID = iM.partID and tPD.hpPartID  <=tpd2.partID)  
WHERE tPD.hpPartID IS NULL  
group by iM.partID, iM.partNumber, iM.unit, iM.description, iM.deptID, iM.sellUp,  iM.longDesc, iM.priceA, iM.netWeight, iM.popCode,tPD.hpPartID

--End qry
0
 
mshughesAuthor Commented:
Thanks again, dbeneit.

This statement also needed to declare the variable for the select max statement i rewrote.  I also needed to use BEGIN and END arguments to get both statements to execute after the IF NOT EXISTS stmt.  It now executes as such:

-- update existing records in tPD
UPDATE    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 *
          FROM iMaster LEFT JOIN tblPartDetail  on tblPartDetail.hpPartID = iMaster.partID
          WHERE tblPartDetail.hpPartID  is null)

BEGIN

-- Get high ID value for new record insertion
DECLARE @nextPartNumber AS NUMERIC
SELECT @nextPartNumber = ISNULL((MAX(partID)+1),100000) FROM tblPartDetail

-- Insert records which do not exist in tPD into iM
INSERT INTO tblPartDetail
(partID,hpPartID,hePartNumber,engineTypeID, partType,partName,unitOfMeasure,shortDescription,price,netWeight,displayStatus)
SELECT @nextPartNumber,iM.partID, iM.partNumber, iM.deptID, iM.sellUp, iM.description, iM.unit, iM.longDesc, iM.priceA, iM.netWeight, iM.popCode
FROM iMaster iM LEFT JOIN  tblPartDetail tPD ON (tPD.hpPartID = iM.partID) LEFT JOIN tblPartDetail tPD2 ON (tPD2.hpPartID = iM.partID and tPD.hpPartID  <=tpd2.partID)  
WHERE tPD.hpPartID IS NULL  
group by iM.partID, iM.partNumber, iM.unit, iM.description, iM.deptID, iM.sellUp,  iM.longDesc, iM.priceA, iM.netWeight, iM.popCode,tPD.hpPartID

END

0
All Courses

From novice to tech pro — start learning today.