增加数据库表字段的sql语句
下面这个语句是从一个表的某列取记录,把所有记录增加为要改变表的字段
第一个参数是要改变的表名称
第二个参数是要从哪个表选择列
第三个参数是要选择的列名
第四个参数是要增加字段的数据类型。如:char(12)
alter proc alters @table char(20),@tablename char(20),@columns char(20),@type char(20)
as
begin tran
declare @sql varchar(150)
create table m(a char(30),idint int)
declare @id int
select @id=id from sysobjects where name=@table
insert into m(idint) values(@id)
insert into m(a) select name from syscolumns where id=(select idint from m)
delete from m where idint=@id
select @sql='select distinct '+@columns +' as col from '+@tablename
select @sql='select * into t from ( '+@sql+' ) as a'
exec (@sql)
select @sql='delete from t where t.col in (select a from m)'
exec (@sql)
select @sql='alter table t add bh int identity(1,1)'
exec (@sql)
create table #t(a int,b char(20))
select @sql='insert into #t(a) select count(*) from t'
exec (@sql)
declare @i int
select @i=1
declare @shu int
select @shu= a from #t
while (@i<=@shu)
begin
select @sql='update #t set b= t.col from t where t.bh='+convert(char,@i)
exec (@sql)
declare @names char(20)
select @names = b from #t
select @sql='alter table '+@table +' add '+@names+' '+@type
exec (@sql)
select @i=@i+1
end
select @sql='drop table t'
exec (@sql)
select @sql='drop table m'
exec (@sql)
drop table #t
commit