sql - Insert table record with auto generate foreign id -

i have table called duty (columns: dutyid, dutyname, staffid) , table called staff (columns: staffid, staffname)

in order fair, each of staff auto assigned each duty entry (record). should whenever insert duty entry, auto assign staffid (sequential) it.

example

• staffid : 1 staffname: jack
• staffid : 2 staffname: mary

so when insert new entry (first entry) duty, auto insert staffid = 1 duty table. second entry, staffid 2.

and following entry, keep looping staffid sequentially.

desired answer:

dutyid   dutyname   staffid    1      cleaning     1    2      cleaning     2    3      cleaning     1    4      cleaning     2    5      cleaning     1    6      cleaning     2    7      cleaning     3       new staff   8      cleaning     1    9      cleaning     2   10      cleaning     3  

can show , explain me should in stored procedure... thanks

try :

select * duty;  declare @dutyname varchar(20)='cleaning' declare @staffid int declare @maxstaffid int  select @staffid=staffid duty dutyid= (select max(dutyid) duty);   select @maxstaffid=max(staffid) staff;  print @maxstaffid;   insert duty (dutyname, staffid) select @dutyname, case   when @staffid=@maxstaffid (select min(staffid) staff)   else (select min(staffid) staff staffid>@staffid) end ;  select * duty;