insert into newtable select distinct * from oldtable
ft 没有好的方法啦? 用select distinct是表面文章阿,会有n多的无用记录阿
其实,newwen(玩一会儿)得建议挺好的: 1)insert into newtable select distinct * from oldtable 2)删除旧表 3)把newtable 的名改为oldtable的表明就行了。
select field1,field2...fieldn into #t1 from t1 group by field1,fiel2...fieldn truncate table t1 insert into t1 select * from #t1
以前见过一位大哥是这样做的 如果表里有DEFAULT等约束,用select ... into ...时会丢失这些约束,所以建议用以下方法:exec sp_dboption yourDBName,'select into/bulkcopy',true go select distinct * into newtable from tablename go truncate table tablename go insert tablename insert * from newtable go drop table newtable go --如果原来select into/bulkcopy设置为false --exec sp_dboption yourDBName,'select into/bulkcopy',false --go
delete from table_name where rowid not in (select max(rowid) from table_name group by field1,field2...)﹔
select distinct * from oldtable
没有好的方法啦?
用select distinct是表面文章阿,会有n多的无用记录阿
1)insert into newtable select distinct * from oldtable
2)删除旧表
3)把newtable 的名改为oldtable的表明就行了。
from t1
group by field1,fiel2...fieldn
truncate table t1
insert into t1 select * from #t1
如果表里有DEFAULT等约束,用select ... into ...时会丢失这些约束,所以建议用以下方法:exec sp_dboption yourDBName,'select into/bulkcopy',true
go
select distinct * into newtable from tablename
go
truncate table tablename
go
insert tablename
insert * from newtable
go
drop table newtable
go
--如果原来select into/bulkcopy设置为false
--exec sp_dboption yourDBName,'select into/bulkcopy',false
--go
where rowid not in
(select max(rowid) from table_name
group by field1,field2...)﹔