sql server - Linking on 0 or more missing data fields -

i have huge master table (1tb of data), , on 20 columns. have source data comming in 0 or more missing data fields. have fill in missing data matching available fields in master table. please consider following example:

incoming data:

 create table #t1 ( f3 varchar(50), f1 int, f2 int )     insert #t1      select 'row1',1, null      union      select 'row2', 1, 2  

master table:

create table #t2 ( f1 int, f2 int, f3 int, f4 varchar(255)  )   insert #t2  select 1, 2, 3, 'a'  union  select 1, 2, 4, 'b'  union  select 1, 3, 3, 'c'  union  select 1, 3, 4, 'd'  union  select 2, 3, 4, 'e'  

i want output link row1 rows a,b,c , d , row2 rows , b. can achieve by:

select a.f3, b.*    #t2 b         cross join #t1   patindex(isnull(convert(char(1), a.f1), '_') + isnull(convert(char(1), a.f2), '_')                 , convert(char(1), b.f1) + convert(char(1), b.f2)) != 0   drop table #t1  drop table #t2  

this not scale-able solution, turn long , complex sql when have 20 fields.

is there better solution?

here "one trick" of trade.

update columns, if no value (a null value) given potential update value....ignore , use original value....using case statement.

your language in original post little confusing.

i think "fields" mean "columns".......(i'm not nitpicking, trying distinquish between column(s) , "values-in-the-column".)

maybe below can help.

here generic northwind example

use northwind go    if object_id('tempdb..#orderdetailsholder') not null begin         drop table #orderdetailsholder end   create table #orderdetailsholder ( identitykey int not null identity (1001, 1),  [orderid] int,  [productid] int, [unitprice] money )   insert #orderdetailsholder ( orderid, productid , unitprice )  select top 10 orderid, productid , unitprice dbo.[order details] unitprice not null   print 'before massaged data look' select * #orderdetailsholder  update #orderdetailsholder set [unitprice] = null identitykey < 1006 update #orderdetailsholder set [unitprice] = ([unitprice] * 1.333) identitykey >= 1006   print 'after massaged data look' select * #orderdetailsholder   /*  here magic (below query).   if [unitprice] null in "holder" temp table, keep original value.   if unitprice not null in temp table, update using holder-temp table unitprice value. */  update dbo.[order details]  set [unitprice] = case                     when holder.[unitprice] not null holder.unitprice                     else realtable.unitprice                 end         #orderdetailsholder holder , dbo.[order details] realtable     holder.orderid = realtable.orderid , holder.productid = realtable.productid    /* show real table data, should have 5 rows having different unitprice values */  select *   #orderdetailsholder holder join dbo.[order details] realtable     on holder.orderid = realtable.orderid , holder.productid = realtable.productid       if object_id('tempdb..#orderdetailsholder') not null begin         drop table #orderdetailsholder end