之前没转过弯,用了游标,速度实在不理想,突然脑子亮了, 10万条,转移+删除,4秒
if OBJECT_ID('tempdb..#tmp') is null --临时表是否存在
begin
--创建临时表, 以防止删除时,数据的改变, 此处剔除最新的一份数据
select * INTO #tmp from ( select dp.id,dp.Latitude,dp.longitude,dp.nozonetimes,dp.Devicestatus,dp.Deviceid from DevicePoint dp
inner join
(select Deviceid, MAX(nozonetimes) nozonetimes from DevicePoint group by deviceid) dn
on dp.deviceid = dn.deviceid and dp.nozonetimes != dn.nozonetimes
) as dn
end
begin TRANSACTION --开始事务
SET XACT_ABORT ON -- 表示遇到错误立即回滚
insert into DP_20161207145202(Latitude,longitude,nozonetimes,Devicestatus,Deviceid) select Latitude,longitude,nozonetimes,Devicestatus,Deviceid from #tmp
delete from devicepoint where id in (select id from #tmp )
IF @@error <> 0 --发生错误
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
if OBJECT_ID('tempdb..#tmp') is not null
Begin
DROP TABLE #tmp
End