Paul,
Sergey has already provided a good solution.
As your transformation needs increase I sense your need of speed also increases (a 100000*250 table1 could lead a 25M recs table3). So here is another solution (longer code but expected to be faster - array transformation for bulk insertion) :

* Create a test table and fill it with random values
#Define testCount  1000
#Define fieldCount  100
Rand(-1)
Local ix,jx,lnMaxArrayRecs
Local array aStruc[fieldCount,4]
aStruc = 0
aStruc[1,1] = 'EMPNO'
aStruc[1,2] = 'C'
aStruc[1,3] = '5'
For ix=2 to fieldCount
  aStruc[ix,1] = 'A'+ltrim(str(ix-1))
  aStruc[ix,2] = 'C'
  aStruc[ix,3] = '1'
Endfor
Create table _Table1 from array aStruc
Local array aRecord[fieldCount]
For ix=1 to testCount
  aRecord[1] = padl(ix,5,'0')
  For jx = 2 to fieldCount
    aRecord[jx] = iif(int(rand()*100)%2=1, '',chr(asc('A')+(rand()*26)))
  Endfor
  Insert into _Table1 from array aRecord
Endfor
* Create a test table and fill it with random values

* At this point we assume _Table1 exists and do _Table2 and 3 transformations
Select _Table1
Afields(aStrucTable1)
For ix=2 to alen(aStrucTable1,1)
  aStrucTable1[ix,1] = stuff(aStrucTable1[ix,1],1,1,'B') && Change fieldnames
  aStrucTable1[ix,3] = 5 && Change fieldsizes
Endfor
Create table _Table2 from array aStrucTable1
* Calculate how many max records we could put in array
* 65000 is max array elem capacity up to current versions
* (fcount()-1)*2 for we would need array rows like EMPNO, Bn
lnMaxArrayRecs = floor(65000/((fcount('_Table1')-1)*2))
Create table _Table3 (empno c(5), field1 c(5))
Select _Table1
lnRows3 = fcount('_Table1')-1
Scan
  Release arrTransfer && Ensure old transfer elems not preserved
  lnPos = recno()
  Copy to array arrTransfer next min(lnMaxArrayRecs, reccount()-lnPos+1)
  For ix=1 to alen(arrTransfer,1)
    For jx=2 to fcount('_Table1')
      arrTransfer[ix,jx] = iif(empty(arrTransfer[ix,jx]),'',;
                           field(jx,'_Table1')+arrTransfer[ix,jx])
    Endfor
  Endfor
  Insert into _Table2 from array arrTransfer
  * Transfer the portion into _Table3
  Dimension arrTransferTo3[alen(arrTransfer,1)*(fcount('_Table1')-1), 2]
  For ix=1 to alen(arrTransfer,1)
    For jx=2 to alen(arrTransfer,2)
      arrTransferTo3[lnRows3*(ix-1)+jx-1,1]=arrTransfer[ix,1]
      arrTransferTo3[lnRows3*(ix-1)+jx-1,2]=arrTransfer[ix,jx]
    Endfor
  Endfor
  Select _Table3
  Append from array arrTransferTo3 for !empty(field1)
  Select _Table1
Endscan

PS: Since both transformations could be done on the fly I wonder why you need to create tables (of course always there is a reason:)
Cetin

>Cetin
>
>Thank you very much,
>
>I tried it and works fine but I have another question:
>
>How to convert from TABLE2 TO TABLE3 to have many records like :
>
>TABLE2 :
>
>EMP# B1 B2 B3 B4 ............B....
>
>00001 A1X A2X A3X -
>
>00002 A1Y A4Y - -
>
>TABLE3 should have many records for the same emp#
>
>emp#
>
>00001 A1X
>00001 A2X
>00001 A3X
>.
>.
>00002 A1Y
>00002 A4Y
>.
>.
>I really appreciate if you could help me out.
>Thanks in advance.
>Paul
>
>after these codes :
>
>copy to table2
>use Table2 excl
>for ix=2 to fcount()
> lcFieldName = field(ix)
> lcNewName = stuff(lcFieldName,1,1,'B')
> replace all (lcFieldName) with ;
> iif(empty(&lcFieldName),'',lcFieldName+&lcFieldName)
> alter table Table2 rename column &lcFieldName to &lcNewName
>endfor
>
>to produce TABLE3.