一个int型字段，原先不是identity型的，如何用SQL语句将其改为identity型

RT 问题点数：40、回复次数：3Top

1 楼zjcxc（邹建）回复于 2005-06-04 16:06:59 得分 20

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_ToIDENTITY]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)  
  drop   procedure   [dbo].[p_ToIDENTITY]  
  GO  
   
  /*--普通列转换为标识列  
   
  采用修改表名，然后按新规则建表的方法处理  
  只适用于没有与其他对象有关联的表  
  转换后，列的相关对象也不会恢复  
  因此这种处理有很大局限性，仅供参考  
   
  --邹建   2005.05(引用请保留此信息)--*/  
   
  /*--调用示例  
   
  EXEC   p_ToIDENTITY   'tb','id'  
   
  --使用测试  
   
  --创建测试的表  
  create   table   tb(编号   bigint,姓名   varchar(10))  
  insert   tb   select   1,'张三'  
  union   all   select   2,'李四'  
  union   all   select   4,'王五'  
  go  
   
  --调用存储过程,将编号字段改为标识字段  
  exec   p_ToIDENTITY   'tb','编号'  
  go  
   
  --显示处理结果  
  select   *   from   tb  
   
  --显示是否修改成功  
  select   name   from   syscolumns    
  where   object_id('tb')=id   and   status=0x80  
  go  
   
  --删除测试  
  drop   table   tb  
  --*/  
  CREATE   PROC   p_ToIDENTITY  
  @TableName   sysname,   --要处理的表名  
  @FieldName   sysname,   --要转换的列名,必须是整型数据字段  
  @increment   int=1         --标识值的增量  
  AS  
  IF   ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0  
  BEGIN  
  RAISERROR('"%s"   必须是当前数据库中已经存在的用户表',12,16,@TableName)  
  RETURN  
  END  
   
  --标识列转换处理检查  
  DECLARE   @s1   nvarchar(1000),@s2   nvarchar(4000),@bkTableName   sysname  
  SELECT   @s2='',@bkTableName=CAST(NEWID()   as   char(36)),  
  @s1=CASE  
  WHEN   c.status=0x80    
  THEN   N'表"%s"中已经有标识列'+QUOTENAME(c.name,N'"')  
  +N',列"%s"不能再被转换成标识列'  
  WHEN   t.name   LIKE   '%int'    
  THEN   N','+QUOTENAME(c.name)  
  +N'=IDENTITY('+QUOTENAME(t.name)  
  +N',1,'+CAST(@increment   as   varchar)+N')'  
  WHEN   c.scale=0   AND   t.name   IN(N'decimal',N'numeric')    
  THEN   N','+QUOTENAME(c.name)  
  +N'=IDENTITY('+QUOTENAME(t.name)  
  +N'('+CAST(c.prec   as   varchar)+N')'  
  +N',1,'+CAST(@increment   as   varchar)+N')'  
  ELSE   N'表"%s"中的列"%s"定义"'+QUOTENAME(t.name)  
  +CASE   WHEN   c.scale>0    
  THEN   N'('+CAST(c.prec   as   varchar)  
  +N','+CAST(c.scale   as   varchar)+N')'  
  ELSE   N''   END  
  +N'无效，不能转换为标识列'  
  END  
  FROM   sysobjects   o,syscolumns   c,systypes   t  
  WHERE   o.name=@TableName  
  AND   o.id=c.id  
  AND   c.xusertype=t.xusertype  
  AND(c.name=@FieldName   OR   c.status=0x80)  
  IF   @@ROWCOUNT=0  
  BEGIN  
  RAISERROR(N'表   "%s"   中无列   "%s"',12,16,@TableName,@FieldName)  
  RETURN  
  END  
  IF   LEFT(@s1,1)<>','  
  BEGIN  
  RAISERROR(@s1,12,16,@TableName,@FieldName)  
  RETURN  
  END  
   
  --生成要处理的列  
  SELECT   @s2=@s2+N','+QUOTENAME(c.name)  
  FROM   sysobjects   o,syscolumns   c  
  WHERE   o.name=@TableName  
  AND   o.id=c.id  
  ORDER   BY   c.colid  
  SELECT   @s1=REPLACE(@s2+N',',N','+QUOTENAME(@FieldName)+N',',@s1+N','),  
  @s1=SUBSTRING(@s1,2,LEN(@s1)-2),  
  @s2=STUFF(@s2,1,1,N'')  
   
  --修改原表名  
  EXEC   sp_rename   @TableName,@bkTableName  
   
  --转换为标识列处理  
  SELECT   @TableName=QUOTENAME(@TableName),  
  @bkTableName=QUOTENAME(@bkTableName)  
  EXEC('  
  SELECT   '+@s1+'   INTO   '+@TableName+N'    
  FROM   '+@bkTableName+'   WHERE   1=0  
  SET   IDENTITY_INSERT   '+@TableName+N'   ON  
  INSERT   '+@TableName+N'('+@s2+N')  
  SELECT   '+@s2+N'   FROM   '+@bkTableName+N'  
  SET   IDENTITY_INSERT   '+@TableName+N'   OFF  
  DROP   TABLE   '+@bkTableName)  
  GO  
   
  Top

2 楼zjcxc（邹建）回复于 2005-06-04 16:07:16 得分 0

alter   table不支持在普通列和标识列之间转换.   你可以参考上面的存储过程,但要注意使用存储过程修改的限制.  
  Top

3 楼47522341（睡到8：30）回复于 2005-06-04 16:13:25 得分 20

 
  BEGIN   TRANSACTION  
  CREATE   TABLE   dbo.Tmp_TABLE1  
  (  
  field1   int   NOT   NULL   IDENTITY   (1,   1),  
  field2   nvarchar(50)   NULL  
  )     ON   [PRIMARY]  
  GO  
  SET   IDENTITY_INSERT   dbo.Tmp_TABLE1   ON  
  GO  
  IF   EXISTS(SELECT   *   FROM   dbo.TABLE1)  
    EXEC('INSERT   INTO   dbo.Tmp_TABLE1   (field1,   field2)  
  SELECT   field1,   field2   FROM   dbo.TABLE1   TABLOCKX')  
  GO  
  SET   IDENTITY_INSERT   dbo.Tmp_TABLE1   OFF  
  GO  
  DROP   TABLE   dbo.TABLE1  
  GO  
  EXECUTE   sp_rename   N'dbo.Tmp_TABLE1',   N'TABLE1',   'OBJECT'  
  GO  
  COMMITTop

相关问题

• 字段
• 转换
• 存储过程
• 修改
• bktablename
• toidentity
• identity
• tablename
• 标识
• quotename

• 帖主：lldwolf
• zjcxc
• 47522341

• SQL Server类图书