select identity(int,1,1) as id , * into #tab from 原表insert into 目标表(id,...) select 'M0606'+ right('0000'+ cast(id as varchar),4),... from #tab====================================一条数据也可以,不过语句稍微麻烦一点!
--别忘了 drop table #tab:)
楼上厉害:) select *,identity(int,1,1) as id into # from 表名--加序号 ------------------ insert 表名(id,..) select 'M'+right(106060000+id,8) from #
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 #TSelect * From B Drop Table #T GO Drop Table A,B --Result /* ID Name M06060001 liu M06060002 bb M06060003 cc M06060004 dd M06060005 ee */
paoluo(一天到晚游泳的鱼) 大哥厉害!我没有挖掘到这些信息:(
:) 借用一下paoluo(一天到晚游泳的鱼) 的数据:--一条语句 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
select 'M0606'+ right('0000'+ cast(id as varchar),4),...
from #tab====================================一条数据也可以,不过语句稍微麻烦一点!
drop table #tab:)
select *,identity(int,1,1) as id into # from 表名--加序号
------------------
insert 表名(id,..)
select 'M'+right(106060000+id,8)
from #
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 #TSelect * From B
Drop Table #T
GO
Drop Table A,B
--Result
/*
ID Name
M06060001 liu
M06060002 bb
M06060003 cc
M06060004 dd
M06060005 ee
*/
借用一下paoluo(一天到晚游泳的鱼) 的数据:--一条语句
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