SqlServer大数据量搬移

方法一：分步搬移(针对百万 数据 量) -----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 -S"192.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

飞扬过海