備份歷史報價-排程設定

 

整合取得期交所每日行情資料和備份期交所歷史報價的內容，再來作些設計，讓全部作業自動化吧。

 

建立一個CMD檔，路徑請自行更改

@echo off
D:\期貨DB建置資料\ExtractorProcess\ExtractorProcess\bin\Debug\ExtractorProcess.exe
cd D:\期貨DB建置資料\期貨Tick\zip
D:
unzip -d D:\期貨DB建置資料\期貨Tick Daily.zip
del Daily.zip
cd..
copy *.rpt D:\期貨DB建置資料\期貨Tick\BACK_new
rename *.rpt Daily.rpt

指令簡單說明，一開始執行 ExtractorProcess.exe 將檔案下載回來，利用unzip這個執行檔把檔案解壓縮，把ZIP檔刪除，然後把解壓縮完的rpt檔複製備份起來，再將這個rpt檔重新命名為Daily.rpt。



然後在SQL SERVER設定排程，如圖，

 



 

第一步是執行將Daily.rpt轉檔到TABLE Daily的封裝，第二步是執行Daily備份到各TABLE的SQL語法，完整SQL程式如下，

 

insert Tick select * from Daily
go

select top 1 expireDate,count(*) num 
into #tmp
from daily 
where commodity = 'TX'
group by expiredate
order by num desc 

select Date,dealtime,dealprice,sum(cast(dealvolume as int)) dealvolume
into #tmp1
from Daily 
where commodity = 'TX' and NearPrice = '-' 
and expiredate = (select expireDate from #tmp)
and dealtime = '084500'
group by Date,Commodity,expiredate,dealtime,dealprice
order by Date,Commodity,expiredate,dealtime,dealvolume desc

insert #tmp1
select Date,dealtime,dealprice,sum(cast(dealvolume as int)) dealvolume
from Daily 
where commodity = 'TX' and NearPrice = '-' 
and expiredate = (select expireDate from #tmp)
and dealtime > '084500'
group by Date,Commodity,expiredate,dealtime,dealprice
order by Date,Commodity,expiredate,dealtime,dealvolume

insert TX_Tick(Date,DealTime,dealPrice,Dealvolume)
select * from #tmp1 order by dealtime

insert TE_Tick(Date,DealTime,dealPrice,Dealvolume)
select Date,dealtime,dealprice,sum(cast(dealvolume as int)) dealvolume
from Daily 
where commodity = 'TE' and NearPrice = '-' 
and expiredate = (select expireDate from #tmp)
group by Date,Commodity,expiredate,dealtime,dealprice
order by Date,Commodity,expiredate,dealtime

insert TF_Tick(Date,DealTime,dealPrice,Dealvolume)
select Date,dealtime,dealprice,sum(cast(dealvolume as int)) dealvolume
from Daily 
where commodity = 'TF' and NearPrice = '-' 
and expiredate = (select expireDate from #tmp)
group by Date,Commodity,expiredate,dealtime,dealprice
order by Date,Commodity,expiredate,dealtime

insert MTX_Tick(Date,DealTime,dealPrice,Dealvolume)
select Date,dealtime,dealprice,sum(cast(dealvolume as int)) dealvolume
from Daily 
where commodity = 'MTX' and NearPrice = '-' 
and expiredate = (select expireDate from #tmp)
group by Date,Commodity,expiredate,dealtime,dealprice
order by Date,Commodity,expiredate,dealtime

drop table #tmp
go

drop table #tmp1
go

truncate table Daily
go

 

除了第一步L還是有把所有TICK資料存到一個超大的TICK TABLE裡，及後面的TE、TF和台指類似就沒提到之外，其它的相關內容都有在之前說明過了。

 

這個SQL排程執行完後，資料庫就已經備份好歷史資料，最後再一個小小的CMD，把Daily.rpt刪除掉。

 

cd D:\期貨DB建置資料\期貨Tick

D:

del Daily.rpt

 

以上三個步驟，設定排程在每日的下午四點半之後，

1.取得期交所報價檔案的 cmd

2.資料庫排程備份歷史報價

3.清除暫存檔

就自動完成了每日備份囉。