How to formulate T-SQL to exclude duplicates?

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 the duplicate record error.

I tried ur solution but am still getting duplicate error problem. I deleted the 35 rows which were duplicate. What else could be causing this? Here is my query. Part of the confusion I think is that measureid is a single column in j5c_MasterMeasures, but this value comes from two fields in j5c_ListBoxMeasures_Sys.

    CREATE TABLE #GOOD_RECORDS3 (STUDENTID VARCHAR(50), MEASUREDATE SMALLDATETIME, MEASUREID 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
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
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

Answer 1

You have not menstioned the specifics of the unique constraint on J5C_MasterMeasures. Therefore, I assumed that all four columns being inserted were part of the constraint. In addition, your use of Except leads me to believe that you are using SQL Server 2005 or later. In addition, it is not clear how the join to J5C_MeasureNamesV2_Sys fits into the design or solution.

With GoodRecords As
    (
    Select A.StudentId
        , A.measuredate
        , B.measurename+ ' ' +B.LabelName
        , A.score_10 As NCE
    From [J5C_Measures_Sys] A 
        Join [J5C_ListBoxMeasures_Sys] B 
            On A.MeasureID = B.MeasureID
    Where A.StudentId Is Not Null
        And A.Score_10 Is Not Null
        And A.MeasureId Is Not Null
    Group By A.StudentId
        , A.MeasureDate
        , B.MeasureName+ ' ' +B.LabelName
        , A.score_10 
    Having Count(A.Score_10) = 0
    )
Insert J5C_MasterMeasures ( StudentId, MeasureData, MeasureId, NCE )
Select GR.StudentId, GR.MeasureData, GR.MeasureId, GR.NCE
From GoodRecords As GR
    Join [J5C_MeasureNamesV2_Sys] v 
        On v.Score_field_id = 'Score_10'
Where Not Exists    (
                    Select 1
                    From J5C_MasterMeasures As J1
                    Where J1.StudentId = GR.StudentId
                        And J1.MeasureData = GR.MeasureData
                        And J1.MeasureId = GR.MeasureId
                        And J1.NCE = GR.NCE
                    )