求SQL语句急用

create   table   tba(id     int,ptype     char(2),qty   int) 

insert   tba

select   1     ,'A'      ,3     union   all 

select   5     ,'B'      ,2     union   all   

select   4     ,'C'      ,3     union   all   

select   3     ,'D'      ,3     union   all     

select   2     ,'E'      ,6   

select   *   from   tba







select ptype,qty into newta from tba 

drop table tba

create table tba(id int identity(1,1) primary key,ptype char(2),qty int)

insert into tba select * from newta

select * from tba

id          ptype qty

----------- ----- -----------

1           A     3

2           B     2

3           C     3

4           D     3

5           E     6



(5 row(s) affected)

有表XX,想把ID重新生成一下, 

id     ptype     qty 

1         A             3 

5         B             2   

4         C             3   

3         D             3   

2         E             6 

-----------------------------

declare @i int

set @i=0

update 表XX set id=@i,@i=@i+1

if exists (select * from dbo.sysobjects where id=object_id(N'[dbo].[p_DropIDENTITY]') and OBJECTPROPERTY(id,N'IsProcedure')=1) 

drop procedure [dbo].[p_DropIDENTITY] 

GO 

/*--标识列转换为普通列



采用修改标识列名，然后按新规则重列的方法处理

只适用于标识列没有与其他对象有关联的表

转换后，列的相关对象也不会恢复

有一定的局限性，仅供参考



--邹建  2005.05(引用请保留此信息)--*/ 



/*--调用示例



EXEC   p_DropIDENTITY   'tb ' 

--*/ 

CREATE   PROC   p_DropIDENTITY 

@TableName  sysname   --要处理的表名

AS 

IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0 

BEGIN 

RAISERROR( '"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName) 

RETURN 

END 



--标识列转换处理检查

DECLARE   @s   nvarchar(1000),@FieldName   sysname,@bkFieldName   sysname,@sql nvarchar(4000) 

SELECT   @FieldName=QUOTENAME(c.name), 

@bkFieldName=CAST(NEWID()   as   char(36)), 

@s=@FieldName+N''+QUOTENAME(t.name) 

+CASE   WHEN   t.name   LIKE   '%int'THEN   N'' 

ELSE   N'('+CAST(c.prec as varchar) 

+N','+CAST(c.scale   as   varchar)+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.status=0x80 

IF   @@ROWCOUNT=0 

BEGIN 

RAISERROR(N'表"%s" 中无标识列',1,16,@TableName) 

RETURN 

END 



--修改标识列名

SET   @sql=QUOTENAME(@TableName)+N'.'+@FieldName 

EXEC   sp_rename  @sql,@bkFieldName,N'COLUMN ' 



--转换为标识列处理

SELECT   @TableName=QUOTENAME(@TableName), 

@bkFieldName=QUOTENAME(@bkFieldName) 

EXEC( 'ALTER   TABLE   '+@TableName+N'ADD'+@s) 

EXEC( 'UPDATE   '+@TableName+N'SET'+@FieldName+N'='+@bkFieldName+N' 

ALTER TABLE   '+@TableName+N'DROP COLUMN'+@bkFieldName) 

RAISERROR(N'表"%s"中的标识列"%s"已经转换为普通列',1,16,@TableName,@FieldName) 

GO

declare @tb table (id int identity(1,1), ptype char(1), qty int)

insert into @tb select * from XX order by ptype

truncate table XX

insert into XX select * from XX order by id