SQL Server MERGE multiple on clause variations -

i using merge statement insert/update records in database.

my source table contains 3 columns used identify if record needs inserted or updated. problem arises in on clause – target table , source table joined using 3 columns there numerous variations on how joined. (see below example)

i know , / or operators used achieve become messy , difficult maintain. wondering if there more efficient way?

an example of code below:

   merge target_table t    using (select @param1, @param2, @param3)              s (col1, col2, col3)       on (t.col1 = s.col1      , t.col2 = s.col2      , t.col3 = s.col3)       or (t.col1 = s.col2      , t.col2 = s.col1      , t.col3 = s.col3)     when  matched     […update…]     when      not  matched     […insert…] 

thanks in advance!

you build source table differently, rather using or build combinations before merge condition. e.g.

declare @param1 int = 1,          @param2 int = 2,          @param3 int = 3;      params (   select  pvalue, pnumber        (values (@param1, 1), (@param2, 2), (@param3, 3)) p (pvalue, pnumber) ) select  col1 = p1.pvalue, col2 = p2.pvalue, col3 = p3.pvalue    params p1         inner join params p2             on p2.pnumber not in (p1.pnumber)         inner join params p3             on p3.pnumber not in (p1.pnumber, p2.pnumber); 

this gives 6 combinations (with each join making sure not re-using same parameter):

col1    col2    col3 ------------------ 2       3       1 3       2       1 1       3       2 3       1       2 1       2       3 2       1       3 

so merge condition become simpler:

with params (   select  pvalue, pnumber        (values (@param1, 1), (@param2, 2), (@param3, 3)) p (pvalue, pnumber) ), sourcetable (   select  col1 = p1.pvalue, col2 = p2.pvalue, col3 = p3.pvalue        params p1             inner join params p2                 on p2.pnumber not in (p1.pnumber)             inner join params p3                 on p3.pnumber not in (p1.pnumber, p2.pnumber) ) merge target_table t using sourcetable s     on t.col1 = s.col1     , t.col2 = s.col2     , t.col3 = s.col3 when matched […update…] when not matched […insert…]; 

this makes easier add further parameters, 4th parameter require row in cte params, rather whole new set of or conditions

declare @param1 int = 1,          @param2 int = 2,          @param3 int = 3,          @param4 int = 4;      params (   select  pvalue, pnumber        (values (@param1, 1), (@param2, 2), (@param3, 3), (@param4, 4)) p (pvalue, pnumber) ) select  col1 = p1.pvalue, col2 = p2.pvalue, col3 = p3.pvalue    params p1         inner join params p2             on p2.pnumber not in (p1.pnumber)         inner join params p3             on p3.pnumber not in (p1.pnumber, p2.pnumber); 

which gives 24 combinations, without code.