求一SQL语句..

[swcsoft]

现有一数据库有200多表,其中每个表都有1万多条数据..

小弟现在想删除这些数据,但每个表的数据都要留100条..请问这个如何删除.谢谢..

[crazyflower]

delete 表名 where id not in (select top 100 id from 表名)

[OracleRoob]

循环每个表，然后用

delete 表名 where checksum(*) not in (select top 100 checksum(*) from 表名)

--注意表之间是否有外键约束。

[swcsoft]

wangtiecheng(不知不为过，不学就是错！)

如何循环呢??小弟是初学者..谢谢了..

[OracleRoob]

循环每个表，然后用

delete 表名 where checksum(*) not in (select top 100 checksum(*) from 表名)

--注意表之间是否有外键约束。
-表中不能有字段类型：text、ntext、image、cursor

[OracleRoob]

--所有用户表

select * from Sysobjects where xtype='U' and name<>'dtproperties'

[swcsoft]

wangtiecheng(不知不为过，不学就是错！)

谢谢了~~我试试..

[OracleRoob]

--循环每个表，注意表之间是否有外键约束。
--且：表中不能有字段类型：text、ntext、image、cursor


declare @sql varchar(8000)

declare test_cursor cursor read_only
for
select name from sysobjects where xtype='u' and name<>'dtproperties'


declare @name varchar(100)
open test_cursor

fetch next from test_cursor into @name
while (@@fetch_status =0)
begin

set @sql = 'delete ' + @name + ' where checksum(*) not in (select top 100 checksum(*) from ' + @name + ')'
exec (@sql)

fetch next from test_cursor into @name
end

close test_cursor
deallocate test_cursor



select 'OK'

[子陌红尘]

不考虑表与表之间的关联问题：

declare @sql varchar(8000),@name varchar(40)
declare t_cursor cursor for
select name from sysobjects where xtype='U' and name<>'dtproperties'

open t_cursor

fetch next from t_cursor into @name

while @@fetch_status=0
begin
set @sql='
select top 100 * into # from '+@name+'

truncate table '+@name+'

insert into '+@name+' select * from #'

exec(@sql)

fetch next from t_cursor into @name
end

close t_cursor
deallocate t_cursor

[OracleRoob]

如果有自动编号字段的话，楼上这种写法好像就有问题了。

[swcsoft]

是有自动编号的字段..在此感谢两位大哥帮助..谢谢...

我正在用查询分析器删,但好像要超时了..

[wgsasd311]

sp_msforeachtable @command1='alter table ? add ffff timestamp',
@command2='declare @val varbinary(20)
select @val=max(ffff) from (select top 100 * from ?) a
delete from ? where ffff>@val'

[swcsoft]

wgsasd311(自强不息) 不好意思,你迟了,已经结帖了,谢谢各位的帮助了..