解析如何用SQL语句在指定字段前面插入新的字段_MsSql_脚本之家

Posted by

create proc addcolumn@tablename varchar, –表名@colname varchar,
–要加的列名@coltype varchar, –要加的列类型@colid int
–加到第几列asdeclare @colid_max intdeclare @sql varchar
–动态sql语句————————————————–if not
exists(select 1 from sysobjectswhere name = @tablename and xtype =
‘u’)beginraiserror 20001 ‘没有这个表’return
-1end————————————————–if exists(select
1 from syscolumnswhere id = object_id and name =
@colname)beginraiserror 20002 ‘这个表已经有这个列了!’return
-1end————————————————––保证该表的colid是连续的select
@colid_max = max from syscolumns where id=object_idif @colid >
@colid_max or @colid < 1set @colid = @colid +
1————————————————–set @sql = ‘alter
table ‘+@tablename+’ add ‘+@colname+’ ‘+@coltypeexecselect @colid_max =
colidfrom syscolumns where id = object_id and name = @colnameif
@@rowcount <> 1beginraiserror 20003
‘加一个新列不成功,请检查你的列类型是否正确’return
-1end————————————————––打开修改系统表的开关EXEC
sp_configure ‘allow updates’,1 RECONFIGURE WITH
OVERRIDE–将新列列号暂置为-1set @sql = ‘update syscolumnsset colid =
-1where id = object_idand colid = ‘+cast(@colid_max as
varchar–将其他列的列号加1set @sql = ‘update syscolumnsset colid =
colid + 1where id = object_idand colid >=
‘+cast)exec–将新列列号复位set @sql = ‘update syscolumnsset colid =
‘+cast)+’where id = object_idand name = ”’+@colname
+””exec————————————————––关闭修改系统表的开关EXEC
sp_configure ‘allow updates’,0 RECONFIGURE WITH
OVERRIDEgo调用方法:exec addcolumn
‘表名’,’新列名’,’新列类型’,加到第几个位置如:exec addcolumn
‘test’,’id2′,’char’,2表示将id2这个列加到表test的第二个位置,类型是char。

create procedure DataFormat
 @tablename varchar(100)
as
begin
declare @Columns table
(
   [name] varchar(100)
)
declare @sql varchar (max)
declare @CurrentColumnName varchar(100)
set @CurrentColumnName=null
insert into @Columns select b.name  from sysobjects a inner join
syscolumns b on a.id=b.id where a.name=@tablename and a.xtype=’U’ and
b.xtype=’167′

declare MyCursor Cursor for select [name] from @Columns for read
only
open MyCursor
fetch next from MyCursor into @CurrentColumnName
while @@fetch_status = 0
begin
   set @sql=’update ‘+ @tablename +’ set '+@CurrentColumnName+' =
cast(
   cast(substring('+@CurrentColumnName+',1,CHARINDEX(''e'','+@CurrentColumnName+')-1)
as decimal(30,10))*
   power(cast(10 as
decimal(30,10)),cast(substring('+@CurrentColumnName+',CHARINDEX(''e'','+@CurrentColumnName+')+1,len('+@CurrentColumnName+')-CHARINDEX(''e'','+@CurrentColumnName+'))
as decimal))
      
   as decimal(30,2)) where '+@CurrentColumnName+ ‘ like ”%e%”’
 print(@sql)
 exec(@sql)
 if @@error<>0
 begin
  close MyCursor
  deallocate MyCursor
  return
 end
 fetch next from MyCursor into @CurrentColumnName    
end
close MyCursor
deallocate MyCursor

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注