--一条语句
insert into b
select 'M'+ Convert(Varchar(4),GetDate(),12)+Right(10000+ID,4),Name
from
(select id = (select count(1) from a where name <= t1.name),name
from a t1
)t2
--结果:select * from b
M06060005 liu
M06060001 bb
M06060002 cc
M06060003 dd
M06060004 ee
Create Table A(Name Varchar(50))
Insert A Select 'liu'
Union All Select 'bb'
Union All Select 'cc'
Union All Select 'dd'
Union All Select 'ee'
Create Table B(ID Char(9),Name Varchar(50))
GO
Select ID=Identity(Int,1,1),* Into #T From A
Insert B Select 'M'+ Convert(Varchar(4),GetDate(),12)+Right(10000+ID,4),Name From #T
Select * From B
Drop Table #T
GO
Drop Table A,B
--Result
/*
ID Name
M06060001 liu
M06060002 bb
M06060003 cc
M06060004 dd
M06060005 ee
*/