insert the last status of a service of users to another table in SQL Server -

insert the last status of a service of users to another table in SQL Server -

i have table in sql database , insert service activation log users. example: service deactivate mike today activate him tomorrow , deactivate again. need have lastly status of each service users in table. (unique constraint: userid,serviceid)

logid | userid | serviceid | status -------+-------------+-------------+-------- 1 | mike | | 0 2 | mike | b | 1 3 | mike | b | 0 4 | mike | | 1 5 | dave | c | 1 6 | dave | | 0 7 | mike | d | 1 8 | mike | c | 1 9 | mike | | 0

for example: in above table, need have next table:

userid | serviceid | laststatus -------+-------------+-------------+-------- mike | | 0 mike | b | 0 mike | c | 1 mike | d | 1 dave | c | 1 dave | | 0

is there while loop read records table1 , insert or update table2 store lastly status of each service users? this:

while (select * table1) { if exists ( select 1 table2 table2.userid=table1.userid , table2.serviceid=table1.serviceid ) begin update table2 set table2.laststatus = table1.status table2.userid=@userid , table2.serviceid=table1.serviceid end else insert table2 values ( table1.userid ,table1.serviceid ,table1.status ) }

last status status largest logid given user , service

this can solved merge statement, so:

merge table2 tgt using ( select * table1 logid in ( --get row has lastly status user-service combination select distinct max(logid) on (partition userid, serviceid order userid,serviceid) mid table1) ) src on tgt.userid = src.userid , tgt.serviceid = src.serviceid when matched update set tgt.[status] = src.[status] when not matched insert (userid,serviceid, [status]) values(src.userid, src.serviceid, src.[status]);

demo here.

sql sql-server