Limit insert of column value

See more:

SQL

I have the following query below that is inserting correctly the way it is written. However, I need to modify to only insert the value of column 'ufid' set number of times. For example, the value should only be in the table 40 times.

For example if the ufid = 'K7'. I need to limit the number of times the 'K7' can be inserted. If 'K7' has already been inserted into the database 40 times, then the next ufid should be selected based on the where clause criteria. Ideas?? Please

<pre>WITH Match_NomineesWithReviewers AS
(
    SELECT 
        [AppID], 
        RTRIM(Major) AS Major,
        COUNT(1) AS rowcnt
    FROM  
        #mult_nuf
    GROUP BY
        [AppID], 
        RTRIM(Major)
)
, rownum_matches AS (
SELECT 
    m.[AppID], 
    r.ufid,
    m.rowcnt,
    ROW_NUMBER() OVER (PARTITION BY m.[AppID] ORDER BY newid()) AS rownum
FROM 
    Match_NomineesWithReviewers m
JOIN
    #temp_rUF t ON t.sh_Plan != m.Major
 )
INSERT into #TempNTable
SELECT [AppID], ufid FROM rownum_matches rm WHERE rownum <= rowcnt

What I have tried:

I would rather use a different alternative to a cursor but I am drawing a blank. I have tried this cursor with no luck (THE Update query is commented out because it is written incorrectly):

DECLARE @ReviewerCount CURSOR
DECLARE @New_ufID varchar(8)
SET @ReviewerCount = CURSOR FAST_FORWARD FOR
SELECT ufid from #TempNTable
group by ufid
having count(ufid) > 40;

open @ReviewerCount

FETCH NEXT FROM @ReviewerCount INTO @New_ufID
set @RevCursorStatus = @@FETCH_STATUS

WHILE @RevCursorStatus = 0 begin


declare @OverageReviewers int
set @OverageReviewers = (SELECT COUNT (A.ufid) FROM
(SELECT COUNT(ufid) AS uf_id FROM #TempNTable
GROUP BY ufid
HAVING COUNT(ufid) > 40) as A
GROUP BY ufid)


while (@OverageReviewers > 0) BEGIN


--UPDATE #TempNTable
--SET UFID = @New_ufID
--from #temp_rUF t
-- ON t.short_plan != major)
--SET @OverageReviewers = @OverageReviewers - 1
--set @RevCursorStatus = -1

end
END