Name         School            id

maul          800             10001

maul          800             10002

paul          800             20000

maul         1800             10001

maul          1800            10002

Name     No.

maul     001

paul     111

maul     002

insert into TZ
(name,id)
 SELECT TN.name, CAST(id AS NVARCHAR(15)) + CAST(Right([no], 1) AS NVARCHAR(1)) AS ID
FROM TN
INNER JOIN TM
ON TN.Name = TM.name.

name   id

maul   10001

maul   10002

paul   20002

maul   100011

maul   100012