sql - Insert Records that are not exists in Target Table and delete records that are not exists in source dataset Using MERGE -

i have table data below (table 1)

id          valueid     value ----------- ----------- -------------------------------------------------- 1           1           value 1 1           1           value 2 1           1           value 3 1           2           value 1 1           2           value 2 1           2           value 3 

and have dataset below (dataset)

id          valueid     value ----------- ----------- ------- 1           1           value 1 1           1           value 2 1           1           value 4 

now need insert records not exists in table 1 (you can see record

id          valueid     value ----------- ----------- ------- 1           1           value 4 

is not in table 1, record should insert) , need delete records table 1 not in dataset

id          valueid     value ----------- ----------- ------- 1           1           value 3 

, without affecting other records (other records means records id=1 , valueid=2).

i have used following t-sql have wrote using merge, inserts missing records deletes records not in dataset

declare @tmp_value table     (       id int ,       valueid int ,       [value] nvarchar(50)     ) ; insert  @tmp_value         ( [id], [valueid], [value] ) values  ( 1, 1, n'value 1' ),         ( 1, 1, n'value 2' ),         ( 1, 1, n'value 3' ),         ( 1, 2, n'value 1' ),         ( 1, 2, n'value 2' ),         ( 1, 2, n'value 3' ) ;  --select  * --from    @tmp_value tv ;    cte           ( select   1 id , 1 valueid , 'value 1' [value]                union                select   1 id , 1 valueid , 'value 2' [value]                union                select   1 id , 1 valueid , 'value 4' [value]              )     merge @tmp_value tv         using cte         on [cte].[id] = [tv].[id]             , [cte].[valueid] = [tv].[valueid]             , [cte].[value] = [tv].[value]         when not matched              insert values   ( id , [valueid] , [value] )         when not matched source              delete ;  select  *    @tmp_value 

expected result:

id          valueid     value ----------- ----------- -------------------------------------------------- 1           1           value 1 1           1           value 2 1           1           value 4 1           2           value 1 1           2           value 2 1           2           value 3 

update :

declare @tmp_value table     (       id int ,       valueid int ,       [value] nvarchar(50)     ) ; insert  @tmp_value         ( [id], [valueid], [value] ) values  ( 1, 1, n'value 1' ),         ( 1, 1, n'value 2' ),         ( 1, 1, n'value 3' ),         ( 1, 2, n'value 1' ),         ( 1, 2, n'value 2' ),         ( 1, 2, n'value 3' ) ;  --select  * --from    @tmp_value tv ;    cte           ( select   1 id , 1 valueid , 'value 1' [value]                union                select   1 id , 1 valueid , 'value 2' [value]                union                select   1 id , 1 valueid , 'value 4' [value]               )     merge @tmp_value tv         using cte         on [cte].[id] = [tv].[id]             , [cte].[valueid] = [tv].[valueid]             , [cte].[value] = [tv].[value]         when not matched              insert values   ( id , [valueid] , [value] )         ;      cte           ( select   1 id , 1 valueid , 'value 1' [value]                union                select   1 id , 1 valueid , 'value 2' [value]                union                select   1 id , 1 valueid , 'value 4' [value]              )     merge @tmp_value tv         using cte         on ([cte].[id] = [tv].[id]             , [cte].[valueid] = [tv].[valueid]            , [cte].[value] = [tv].[value])             or not( [tv].[id] in (select distinct id cte)             , [tv].[valueid] in (select distinct valueid cte))         when not matched              insert values   ( id , [valueid] , [value] )         when not matched source              delete ;  select  *    @tmp_value