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 ISPUBLISHED<>3
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 ISPUBLISHED<>3 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

飞扬过海