0

I'm trying to insert records from 2 tables into another table where the AppID does not already exist in the table I'm inserting into.

I've tried both insert statements below; however, I keep getting the error message:

"Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_tblHQTRR_BusiRisk_AppID'. Cannot insert duplicate key in object 'dbo.tblHQTRR_BusiRisk'. The duplicate key value is (APPID-49348). The statement has been terminated."

What am I doing wrong with my insert statements?

Insert Statements I've tried:

INSERT INTO tblHQTRR_BusiRisk(AppID,AppName,Tier,QTRR,BusiRisk)
SELECT A.AppID, A.AppName, A.TierLevel, A.QTRR, R.BusiRisk
FROM tblApplication A INNER JOIN tblProject P ON A.AppID=P.AppID1 INNER JOIN tblRisk R ON P.ProjID=R.ProjID
WHERE A.AppID NOT IN (SELECT H.AppID FROM tblHQTRR_BusiRisk H) And P.AppID1 NOT IN (SELECT H.AppID FROM tblHQTRR_BusiRisk H) 
And A.AppName IS NOT NULL And R.BusiRisk IS NOT NULL;


INSERT INTO tblHQTRR_BusiRisk(AppID,AppName,Tier,QTRR,BusiRisk)
SELECT A.AppID, A.AppName, A.TierLevel, A.QTRR, R.BusiRisk
FROM tblApplication A INNER JOIN tblProject P ON A.AppID=P.AppID1 INNER JOIN tblRisk R ON P.ProjID=R.ProjID LEFT JOIN tblHQTRR_BusiRisk H ON H.AppID=A.AppID And H.AppName=A.AppName And H.Tier=A.TierLevel And H.QTRR=A.QTRR And H.BusiRisk=R.BusiRisk
WHERE H.AppID IS NULL;
Cœur
  • 37,241
  • 25
  • 195
  • 267
HeatherD
  • 67
  • 6
  • You are trying to insert a record with an AppID that is already in the table. AppID is unique so it can only be in the table once. The error is telling you which ID you are trying to enter twice - (APPID-49348). It probably signifies a problem in the query. – Daniel Gale Apr 19 '18 at 20:11
  • You just want to break the rules you made in your table :) – Ilyes Apr 19 '18 at 20:14
  • The weird thing is that APPID-49348, which is referenced in the error message, is not actually in tblHQTRR_BusiRisk. I tried the Not Exists method Wyatt Shipman mentioned below, but it's still giving me the same error message. – HeatherD Apr 19 '18 at 20:27

1 Answers1

1

I figured out that the reason my statements weren't working was because I had multiple BusiRisk values for each AppID. Therefore, I created the following statement below and it worked! :)

WITH ProjWithMaxDate AS(
    SELECT P.AppID1, MAX(P.ProjID) AS MaxProjID
    FROM tblProject P
    GROUP BY P.AppID1)
INSERT INTO tblHQTRR_BusiRisk (AppID,AppName,Tier,QTRR,BusiRisk)
SELECT P.AppID1
    ,A.AppName
    ,A.TierLevel
    ,A.QTRR
    ,R.BusiRisk
FROM tblApplication A
INNER JOIN tblProject P ON A.AppID = P.AppID1 INNER JOIN tblRisk R ON P.ProjID = R.ProjID INNER JOIN tblTimeline T ON P.ProjID=T.ProjID INNER JOIN ProjWithMaxDate ON P.AppID1=ProjWithMaxDate.AppID1 And P.ProjID=ProjWithMaxDate.MaxProjID 
WHERE NOT EXISTS (SELECT H.AppID 
                            FROM tblHQTRR_BusiRisk H 
                            WHERE H.AppID = A.AppID
                            AND H.AppName = A.AppName
                            AND H.AppID = P.AppID1) 
AND A.AppName IS NOT NULL AND R.BusiRisk IS NOT NULL;
HeatherD
  • 67
  • 6