>Hi VFP users/experts
>
>I need to merge 2 tables of identical structure. Before merging, I need to find rows from table 1 that match rows of table 2 in the primary key fields but not in the non key fields. The table has primary key based on 2 fields and 100 non key fields. Appreciate if you could help me construct SQL to detect rows from the 2 tables with similar primary fields but dissimilar non key fields.
>
>Thanks

select a.pkfield1+a.pkfield2 ;
 from Table1 a, table2 b ;
 where a.pkfield1+a.pkfield2=b.pkfield1+b.pkfield2
*or
select a.pkfield1+a.pkfield2 ;
 from Table1 a join table2 b ;
 on a.pkfield1+a.pkfield2=b.pkfield1+b.pkfield2
*or
select pkfield1+pkfield2 from Table1 ;
 where pkfield1+pkfield2 in ;
 (select pkfield1+pkfield2 from Table2)

Also you could directly do something like :

* Assuming structs might differ
select table2
scatter name oRecord2 memo blank
select Table1
scatter name oRecord1 memo blank
IsSameStruct=compobj(oRecord1,oRecord2) && Warning only field NAME match
if !IsSameStruct
  * Initialize arrCompare - fieldnames in both
  dimension arrCompare[1]
  lnMembers1=amembers(arrMembers1,oRecord1)
  lnMembers2=amembers(arrMembers2,oRecord2)
  for ix=1 to lnMembers1
   if ascan(arrMembers2,arrMembers1[ix]) && Field exists in both
     * Size array to get new element   
     dimension arrCompare[iif(type('arrCompare')='L',0,alen(arrCompare))+1]
     arrCompare[alen(arrCompare)]=arrMembers1[ix] && Set element
   endif
  endfor
endif

select table1
scan for seek(pkfield1+pkfield2,'Table2','pktag')
  scatter name oRecord1 memo
  select table2
  scatter name oRecord2 memo
  select Table1
  if IsSameStruct
    IsRecSame = compobj(oRecord1,oRecord2) && All fields are identical
  else
    * Either structure different or content
    IsRecSame=.T.
    for each cFieldName in arrCompare
     if eval('oRecord1.'+cField) # eval('oRecord2.'+cField) && Content different
      IsRecSame=.F.
      exit && Discussable using exit in code :)
     endif
    endfor
  endif
  if IsRecSame
  *..
  else
  *...
  endif
endscan

And yet another option is to create 2 copies of a cursor having all fields of each table (added say fieldonlyin2) and appending each table to one copy and do a select union then check 'having cnt(*)>1 group by pkfield' to process only dupes.
Cetin