将A表部分数据修改后倒入B表，解决的另开贴送200分
A
a1                 a2       a3     a4     a5
z-12-01       c         m       d       200501

B
b1                           b2               b3  
1-040901-001       9m               200501

B表中的b1字段由三部分组成，第一部分对应A.a1中的第一部分，如果A.a1是z的话就为1，g为2，c为3；第二部分对应A.a5，如果是200501就为040901,200603为200501等等有个对应关系；第三部分是在前两部分相同的情况下自动加一。
B表中的b2字段则对应A.a3，只不过在其前面加个数字。
B.b3=A.a5

------解决方案--------------------
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(a1 varchar(20),a2 varchar(20),a3 varchar(20),a4 varchar(20),a5 varchar(20))
insert into A(a1,a2,a3,a4,a5) values( 'z-12-01 ', 'c ', 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'g-12-01 ', 'c ', 'm ', 'd ', '200601 ')
insert into A(a1,a2,a3,a4,a5) values( 'c-12-01 ', 'c ', 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'z-12-01 ', 'c ', 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'g-12-01 ', 'c ' , 'm ', 'd ', '200501 ')
insert into A(a1,a2,a3,a4,a5) values( 'z-12-01 ', 'c ', 'm ', 'd ', '200601 ')
go
if object_id( 'pubs..B ') is not null
drop table B
go
create table B(b1 varchar(20),b2 varchar(20),b3 varchar(20))
if object_id( 'pubs..C ') is not null
drop table C
go
create table C(a5 varchar(20),b5 varchar(20))
insert into C(a5,b5) values( '200501 ', '040901 ')
insert into C(a5,b5) values( '200601 ', '041001 ')
go

if object_id( 'pubs..D ') is not null
drop table D
go
select id = identity(int,1,1) , * into D from A

declare @count as int
select @count = count(*) from d

declare @i as int
set @i = 1

declare @s1 as varchar(20)
declare @s2 as varchar(20)
declare @s3 as varchar(20)
declare @s4 as varchar(20)
declare @s5 as varchar(20)
while @i <= @count
begin
set @s1 = null
set @s2 = null
set @s3 = null
set @s4 = null
set @s5 = null

select @s1 = left(a1,1) from D where id = @i
if @s1 = 'z ' set @s1 = '1 '
if @s1 = 'g ' set @s1 = '2 '
if @s1 = 'c ' set @s1 = '3 '
select @s2 = C.b5 from D,C where D.id = @i and D.a5 = C.a5
select @s3 = max(b1) from B where left(b1,8) = @s1 + '- ' + @s2
if @s3 is null or len(rtrim(@s3)) = 0 set @s3 = '001 '
else
begin
set @s3 = right( '000 ' + cast(cast(right(@s3,3) as int) + 1 as varchar),3)
end
select @s4 = '9 ' + a3 from D where id = @i
select @s5 = a5 from D where id = @i
insert into b(b1,b2,b3) values(@s1 + '- ' + @s2 + '- ' + @s3,@s4,@s5)
set @i = @i + 1
end

select * from B

drop table A,B,C,D

/*
b1 b2 b3
-------------------- ------------------