How do I merge data from two tables into one using Sql Server 2008? -

i'm using sql merge , trying merge 2 tables single table.

basically, there 3 tables named: t1,t2,t3. i'm attempting t2 table data , t3 table data t1 table using merge.

here code:

merge  daily_so_invoice target1 using  temp_invoice source1 ,temp_so_invoicedetail source2  on  target1.invoice_id = right('00000000'+ convert(varchar,source1.invoice_instance_id),8) , target1.linekey<>'9999' ,  right('0000000'+convert(varchar,source1.invoice_instance_id),7) = right('0000000'+convert(varchar,source2.invoice_instance_id),7) when matched         update          set        target1.batchno =   upper(right('00'+convert(varchar,day(source1.billing_date)),2) + left(datename(month, source1.billing_date), 3))         when not matched  insert  ( invoice_id, linekey, item_unit_price , invoiced , batchno , item_name , item_description , quantity ) values ( right('00000000'+convert(varchar,source2.invoice_instance_id),8), right('0000'+convert(varchar,source2.linekey),4), source2.item_unit_price , 'n' ,   upper(right('00'+convert(varchar,day(source1.billing_date)),2) + left(datename(month, source1.billing_date), 3)), '/'+source2.item_name , source2.item_description , source2.quantity  ); 

i'm getting error @ line has:

using  temp_invoice source1 ,temp_so_invoicedetail source2 

how do merge when using 2 tables?

are looking this?

with ctesource (     select         -- not sure if these 2 columns different?         -- you're joining on 7 characters, taking 8:         right('00000000'+ convert(varchar, source1.invoice_instance_id), 8) invoice_id,         right('00000000' + convert(varchar, source2.invoice_instance_id), 8) invoice_instance_id,         right('0000' + convert(varchar, source2.linekey), 4) linekey,         source2.item_unit_price,         'n' invoiced,         upper(right('00' + convert(varchar, day(source1.billing_date)), 2) + left(datename(month, source1.billing_date), 3)) batchno,         '/' + source2.item_name item_name,         source2.item_description,         source2.quantity             temp_invoice source1         inner join temp_so_invoicedetail source2         on right('0000000' + convert(varchar, source1.invoice_instance_id), 7)           = right('0000000' + convert(varchar, source2.invoice_instance_id), 7) ) merge      daily_so_invoice target     using ctesource source     on target.invoice_id = source.invoice_id     , target.linekey != '9999'  when matched     update      set              batchno =  batchno  when not matched     insert     (         invoice_id,         linekey,         item_unit_price,         invoiced,         batchno,         item_name,         item_description,         quantity     )     values     (         invoice_instance_id,         linekey         item_unit_price ,         invoiced,         batchno,         item_name,         item_description,         quantity      );