方法一：分步搬移(针对百万 数据 量) -----remove data---------- insert into bizoffersearch_insert_temp select top 2000000 id,0 isinsert from bizoffersearch a with(nolock) where ispublished=2 and ispublished3 and tradingservicetype in(1,3,4)
方法一：分步搬移(针对百万数据量)
-----remove data----------
insert into bizoffersearch_insert_temp
select top 2000000 id,0 isinsert
 from bizoffersearch  a with(nolock) where  ispublished>=2 and ispublished3
    and  tradingservicetype in(1,3,4)   and id not like 'js%'
select * into #temp11 from bizoffersearch_insert_temp where 1=2
while (select count(0) from bizoffersearch_insert_temp where isinsert=0)>1
begin
    insert into #temp11
    select top 400 * from bizoffersearch_insert_temp where isinsert=0
    insert into [192.168.1.61].offerhistorybybak.dbo.bizoffersearch_apidbdk_20100709_1
 select * from bizoffersearch  a with(nolock) where id in(select id from #temp11)
update bizoffersearch_insert_temp set isinsert=1 where id in (select id from #temp11)
    truncate table #temp11
    waitfor delay '00:00:00.500'
end
truncate table #temp11
drop table #temp11
truncate table bizoffersearch_insert_temp
-----delete data-------------------
select id,0 isdelete into #temp1 from [192.168.1.61].offerhistorybybak.dbo.bizoffersearch_apidbdk_20100709_1  with(nolock)
select * into #temp11 from #temp1 where 1=2
while (select count(0) from #temp1 where isdelete=0)>1
begin
    insert into #temp11
    select top 400 * from #temp1 where isdelete=0
 delete  bizoffersearch  with(rowlock) where id in  (select id from #temp11)
    update #temp1 set isdelete=1 where id in (select id from #temp11)
    truncate table #temp11
    waitfor delay '00:00:00.500'
end
truncate table #temp11
drop table #temp11
truncate table #temp1
drop table #temp1
方法二：bcp(针对千万以上的数据量)
如果数据量很大，通过分步搬移已经不能满足要求，这时我们可以考虑用bcp
步骤：
1. 将原数据导入到文件（运行时去掉换行，一次w记录）
  bcp select top 5000000 * from bkoffer.dbo.bizoffersearch  a with(nolock)
  where  ispublished>=2 and ispublished3 and  tradingservicetype in(1,3,4) 
  and id not like 'js%'  
  queryout h:\contacts1.txt  -q -s192.168.1.65 -c -t
2.将文件拷贝到备份机器
3.将文件中的数据导入到备份机器上的新表
  bcp offerhistorybybak.dbo.bizoffersearch_apidbdk_20100709 in h:\contacts1.txt -t -c -m 1000
4.删掉原数据（bizoffersearch_insert_temp表只存id和标志位isinsert，值为0或1）
  insert into bizoffersearch_insert_temp
 select id,0 from [192.168.1.61].offerhistorybybak.dbo.bizoffersearch_apidbdk_20100709   with(nolock)
 select * into #temp11 from bizoffersearch_insert_temp where 1=2
 declare @rowcounts int
 select @rowcounts=count(0)/1000 from bizoffersearch_insert_temp where isinsert=0
 while (@rowcounts)>=0
 begin
  insert into #temp11
  select top 1000 * from bizoffersearch_insert_temp where isinsert=0
  delete  bizoffersearch  with(rowlock) where id in  (select id from #temp11)
  update bizoffersearch_insert_temp set isinsert=1 where id in (select id from #temp11)
  truncate table #temp11
  select @rowcounts=@rowcounts-1
  waitfor delay '00:00:00.500'
 end
 truncate table #temp11
 drop table #temp11
 truncate table bizoffersearch_insert_temp
飞扬过海