22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id('ta') is not null
drop table ta
go
create table ta(id CHAR(10))
insert into ta select '001'
insert into ta select '002'
insert into ta select '003'
insert into ta select '004'
if object_id('tb') is not null
drop table tb
go
create table tb(col int)
insert into tb select 1
insert into tb select 2
insert into tb select 3
declare @sql varchar(8000)
select @sql=isnull(@sql+';','')+'alter table tb add ['+id+'] varchar(10)'
from ta group by id
exec(@sql)
set @sql=''
select @sql=@sql+'update tb set ['+id+']=''0'''
from ta group by id
exec(@sql)
select * from tb
create table tb(id CHAR(10) default dbo.getid(),name varchar(20))
create function getid()
returns varchar(10)
as
begin
declare @id varchar(10)
select @id=right('000'+ltrim(isnull(max(id),0)+1),4) from tb with(tablockx)
return @id
end
insert into tb(name) select 'a'
insert into tb(name) select 'b'
insert into tb(name) select 'c'
select * from tb
create table tb(col1 CHAR(1))
insert into tb select 'A'
insert into tb select 'A'
insert into tb select 'A'
alter table tb add col2 varchar(20)
alter table tb add col3 int identity(1,1)
update tb set col2=right('00'+ltrim(col3),3)
alter table tb drop column col3
select * from tb