表a的结构如下:数据类型为int
id a1
1 243
2 9
3 34我想把表a结构变为这样:
id a1 b1
1 243 243
2 9 009
3 34 034也就是把a1列的数据变为b1列的数据的
进行存储.
id a1
1 243
2 9
3 34我想把表a结构变为这样:
id a1 b1
1 243 243
2 9 009
3 34 034也就是把a1列的数据变为b1列的数据的
进行存储.
调试欢乐多
from tablea
insert a select 1,243
union all select 2,9
union all select 3,34 --alter table a alter column b1 add nvarchar(16)alter table a add b1 nvarchar(16) null
goupdate a
set b1=right('000'+rtrim(a1),3)select * from adrop table a/*
id a1 b1
----------- ----------- ----------------
1 243 243
2 9 009
3 34 034(3 row(s) affected)
*/
insert into a select 1,243
union all
select 2,9
union all
select 3,34select id,a1,b1=right(('000'+ltrim(a1)),3)from a
go
update a
set b1=(select right('000'+cast(m.a1 as varchar),3) from a m on m.id=a.id)
declare @t table (id int, a1 int,a2 varchar(5))insert @t select 1,243,''
union all select 2,9,''
union all select 3,34,''--修改前
select * from @t
/*
id a1 a2
----------- ----------- -----
1 243
2 9
3 34 (所影响的行数为 3 行)
*/update @t set a2=right('0000'+cast(a1 as varchar),3)--修改后
select * from @t
/*
id a1 a2
----------- ----------- -----
1 243 243
2 9 009
3 34 034(所影响的行数为 3 行)
*/
declare @1 table (id tinyint,a1 tinyint, b1 as replicate('0',3-len(a1))+ltrim(a1))
insert into @1
select 1,243 union all
select 2,9 union all
select 3,34select * from @1
/*
id a1 b1
---- ---- ----------
1 243 243
2 9 009
3 34 034
*/-->UPDATE实现: @2
declare @2 table (id tinyint,a1 tinyint,b1 varchar(10))
insert into @2
select 1,243,null union all
select 2,9,null union all
select 3,34,nullupdate @2 set b1 = replicate('0',3-len(a1))+ltrim(a1)
select * from @2
/*
id a1 b1
---- ---- ----------
1 243 243
2 9 009
3 34 034
*/