怎样删除表中前1万条记录

--A:假設有主鍵id
--a.删除表中前1万条记录(按id字段排序).
--方法1:
delete TableName where id in(select top 10000 id from TableName order by id)
--方法2:
delete TableName from TableName a join (select top 10000 id from TableName order by id) b on a.id=b.id

--b.删除表中前1万条记录(不按id字段排序).
--方法1:
delete TableName where id in(select top 10000 id from TableName)
go
--方法2:
delete TableName from TableName a join (select top 10000 id from TableName) b on a.id=b.id
go
--方法3:
set rowcount 10000
delete from TableName
set rowcount 0

--B:假設沒有主鍵id
--add column id
alter table TableName add id int not null identity(1,1)
go

--a.删除表中前1万条记录(按id字段排序).
--方法1:
delete TableName where id in(select top 10000 id from TableName order by id)
--方法2:
delete TableName from TableName a join (select top 10000 id from TableName order by id) b on a.id=b.id

--b.删除表中前1万条记录(不按id字段排序).
--方法1:
delete TableName where id in(select top 10000 id from TableName)
go
--方法2:
delete TableName from TableName a join (select top 10000 id from TableName) b on a.id=b.id
go
--方法3:
set rowcount 10000
delete from TableName
set rowcount 0

--drop column id
alter table TableName drop column id
go

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13380151/viewspace-1023812/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13380151/viewspace-1023812/