How to formulate T-SQL to avoid PK constraint?

Question

I am trying to develop a query to just return non-duplicate records so that I can add these to my database, but I keep getting this error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_j5c_MasterMeasures'. 
Cannot insert duplicate key in object 'dbo.j5c_MasterMeasures'.
The statement has been terminated.

And this is my latest query I am using:

CREATE TABLE #GOOD_RECORDS3 (STUDENTID VARCHAR(50), MEASUREDATE SMALLDATETIME, 
measurename VARCHAR(100), LabelName VARCHAR(100), score_10 VARCHAR(100))
INSERT INTO #GOOD_RECORDS3
select A.studentid, A.measuredate, B.measurename, B.LabelName, A.score_10 
from [J5C_Measures_Sys] A join [J5C_ListBoxMeasures_Sys] B on 
A.MeasureID = B.MeasureID
where score_10 is not null and score_10 <> '0' 
except
select A.studentid, A.measuredate, B.measurename, B.LabelName, A.score_10
from [J5C_Measures_Sys] A join [J5C_ListBoxMeasures_Sys] B on 
A.MeasureID = B.MeasureID
where score_10 is not null and score_10 <> '0'
GROUP BY A.studentid, A.measuredate, B.measurename, B.LabelName, A.score_10
having COUNT(A.score_10) > 1

delete #GOOD_RECORDS3
from #GOOD_RECORDS3 a
join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u'
join syscolumns sc on so.id = sc.id and sc.name = 'score_10'
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
WHERE A.SCORE_10 IS NOT NULL AND A.STUDENTID IS NOT NULL AND 
A.MEASUREID IS NOT NULL
and exists (select 1 from J5C_MasterMeasures M
 where M.StudentID = A.StudentID
and M.MeasureID = A.MeasureID)

Insert into J5C_MasterMeasures (studentid, measuredate, measureid, nce)
select A.studentid, A.measuredate, a.MEASUREID, A.score_10
from #GOOD_RECORDS3 a
join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u'
join syscolumns sc on so.id = sc.id and sc.name = 'score_10'
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name
WHERE A.SCORE_10 IS NOT NULL AND A.STUDENTID IS NOT NULL AND 
A.MEASUREID IS NOT NULL

The confusing part about this is that MeasureId from M5C_Measures_Sys = B.measurename + ' ' + B.Labelname. This is some sample data from #GOOD_RECORDS3:

1   2006-03-01 00:00:00 ISAT Reading ISAT Reading   564
10005   2003-11-17 00:00:00 TerraNova Reading TerraNova Reading     19
10005   2003-11-17 00:00:00 TerraNova Science TerraNova Science     26
10005   2003-11-17 00:00:00 TerraNova Total Battery TerraNova Total Battery     22
10005   2003-11-17 00:00:00 TerraNova Total Language TerraNova Total Language   43

So you can see that b.measurename = b.labelname. The error above happens on insert.

Result of your answer query below:

1   2006-03-01 00:00:00 ISAT Reading    ISAT Reading    564
10005   2003-11-17 00:00:00 TerraNova Reading   TerraNova Reading   19
10005   2003-11-17 00:00:00 TerraNova Science   TerraNova Science   26
10005   2003-11-17 00:00:00 TerraNova Total Battery TerraNova Total Battery     22
10005   2003-11-17 00:00:00 TerraNova Total Language    TerraNova Total Language    43

Answer 1

A little out of topic...

Instead of using CREATE TABLE #GOOD_RECORDS3 you can do DECLARE @Good_Records3 TABLE (FieldDefinition...).

If the primarykey is on the column measureid then you have still duplicated ids returned from the query.

What does the query

select A.studentid, A.measuredate, a.MEASUREID, A.score_10 
from #GOOD_RECORDS3 a 
join sysobjects so on so.name = 'J5C_Measures_Sys' AND so.type = 'u' 
join syscolumns sc on so.id = sc.id and sc.name = 'score_10' 
join [J5C_MeasureNamesV2_Sys] v on v.Score_field_id = sc.name 
WHERE A.SCORE_10 IS NOT NULL AND A.STUDENTID IS NOT NULL AND A.MEASUREID IS NOT NULL 

return? No duplicate MEASUREID-values?

Answer 2

Hi,

I consider the problem comes when inserting to J5C_MasterMeasures table the records fetched in to #GOOD_RECORDS3 table. Please be sure what J5C_MasterMeasures PRIMARY KEY is. Then print #GOOD_RECORDS3 records to look for possible duplicates that may violate J5C_MasterMeasures primary key constraint.

Hope that helps,

Answer 3

First declare a table for storing duplicate details in

declare @dupetable table(did int, dcount int)

then declare 2 variables, one for storing the total number of records with duplicates and one as a loop counter

declare @dupecount int, @loopcounter int

then populate the duplicates table with a list of records that have duplicates and the number of duplicates each one has

insert into @dupetable  --populate dupelicate table
(did, dcount)
select id, COUNT(*)
from #GOOD_RECORDS3  
group by id                 --replace ID with whatever the primary key is
having COUNT(*) > 1

next get the total number of records with suplicates

select @dupecount = COUNT(*)    
from @dupetable  

then initialize the loop counter

set @loopcounter = 0

the code should then loop through the table of duplicates, selecting the top record, removing all the duplicate entries for it and then deleting it from the temporary @dupetable

while  @loopcounter < @dupecount   --loop through duplicate records
begin
select * from @dupetable 
set @loopcounter = @loopcounter  + 1    --increase the counter by 1
Declare @ldid int, @ldcount int         --declare variables for storing the duplicate id and the number of dupes
select top 1 @ldid = did, @ldcount = dcount --populate those variables
from @dupetable 
delete from @dupetable      --remove record from dupe table
where did = @ldid 
declare @rcval int      --declare variable for setting rowcount
set @rcval = @ldcount - 1   --set the rowcount val to the number of duplicate rows -1
set rowcount  @rcval        --set the rowcount val to the number of duplicate rows -1
delete from @testtbl        --delete the duplicate records from the table
where id = @ldid
end     --end loop

it's not too elegant but i believe it works