求SQL语句急用

[strongtea]

不懂SQL这个东东的,现在有工作要处理一下,

有表XX,想把ID重新生成一下,
id ptype qty
1 A 3
5 B 2
4 C 3
3 D 3
2 E 6
要生成表YY的结果是:
id ptype qty
1 A 3
2 B 2
3 C 3
4 D 3
5 E 6
应当如何写语句啊?

[tim_spac]

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

[qiuming0306]

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

[qiuming0306]

转成普通列之后，再给他加上自增约束

[JL99000]

if object_id('tb') is not null
drop table tb
go
create table tb(id int, ptype char(2), qty int)
insert tb
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 tb

declare @t table (id int identity(1,1), ptype char(2), qty int)
insert @t select ptype,qty from tb
select * from @t

[pt1314917]

有表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

[火星求索]

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)

[liulang1314521]

alter table Table1 drop column id
alter table table1 add column id int identity(1,1)
把ID列删除掉重建一列自增的。

[gingerkang]

select xx.*,identity(int,1,1) as nid into #t from xx order by ptype
truncate table xx
insert into xx (id,ptype,qty) select nid,ptype,qty from #t
drop table #t