student表生成bj表的sql语句

insert into bj(bjdh)
select distinct substring(id,1,8) as bjdh
from student
where substring(id,1,1)!='P'
union
select distinct substring(id,1,9) as bjdh
from student
where substring(id,1,1)='P'

或
insert into bj(bjdh)
select distinct left(id,len(id)-2) as bjdh
from student

或
insert into bj(bjdh,bjmc)
select distinct left(id,len(id)-2) as bjdh,zymc+right(left(id,len(id)-2),4) as bjmc
from student,zy
where left(id,len(id)-6)=zydh

或
insert into bj(bjdh,bjmc)
select distinct left(id,len(id)-2) as bjdh,zymc+right(left(id,len(id)-2),4) as bjmc
from student,zy
where substring(id,len(id)-5,2)='07' and left(id,len(id)-6)=zydh

注意：bj表其它的字段应该可以为空