SQL Server入庫日誌

-- exec sp_gameLog_import getdate(),'2010-03-01'
-- exec sp_gameLog_import '2010-03-01 10:00','2010-03-01 9:00'--補9點1個小時資料
-- select * from mtunes_elog order by sendtime desc, idx desc, endtime desc

-- exec sp_gameLog_import '2010-03-01','2010-03-01'
Create  proc sp_gameLog_import(
 @endTime datetime=null,-- 每小時25分產生LOG,所以資料入庫相隔2小時才能完整
 @starttime datetime=null--手工錄入注意@endTime的相隔2小時
)
/*遊戲日誌按小時入庫
*/
as
begin
---一次性大量匯入
declare @table varchar(100)
declare @file_path varchar(500)
declare @file varchar(100)
declare @file_exist int
-- declare @startTime datetime
-- declare @endTime datetime
declare @cur datetime

declare @sql varchar(8000)
declare @fieldTerminator varchar(10)
declare @rowTerminator varchar(10)

if @endTime is null
 set @endTime=getdate()
else set @endTime=dateadd(hour, 2, @endTime)--手工錄入通常已確認日誌都包括,補足2小時間隔
-- set @startTime='2010-03-01'
if @startTime is null
 begin  
  --set @startTime =(select max(endTime) from mtunes_elog where type='sp_gameLog_import' and msg='hour import done')
  -- 確保開始時間為最後一次成功入庫檔案的時間的下一個小時
  --if @startTime>(select max(endTime) from mtunes_elog where type='sp_gameLog_import' and msg like 'success:E:/CQ_Notify_Log/ACTION_%')
  set @startTime=(select max(endTime) from mtunes_elog where type='sp_gameLog_import' and msg like 'success:E:/CQ_Notify_Log/ACTION_%')
  set @startTime=dateadd(hour, 1, @startTime)
 end
 --手工產生，刪除已經添加的檔案記錄
 else delete gameLog where aDay+' '+aTime between @startTime and @endTime

-- print convert(varchar, @starttime,120)
-- print convert(varchar, @endTime,120)

-- truncate table gameLog
-- truncate table mtunes_elog
-- select top 0* into gameLog from gameLog_MODEL

set @table='gameLog'
set @file_path='E:/CQ_Notify_Log/ACTION_'
set @fieldTerminator='|'
set @rowTerminator=char(13)+char(10)

set @cur=@starttime
while @cur<dateadd(hour, -2,@endTime)---- 每小時25分產生LOG,所以資料入庫相隔2小時才能完整
begin
--select right(convert(varchar, getdate(), 112),6)+'_'+left(convert(varchar, getdate(), 114),2)
 set @file=@file_path+right(convert(varchar, @cur, 112),6)+'_'+left(convert(varchar, @cur, 114),2)+'.log' 

 -- 判斷檔案是否存在
 exec  master.dbo.xp_fileexist @file,@file_exist output
  if(@file_exist=1)
  begin
   set @sql='bulk insert '+@table+' from '''+@file+'''
    with (fieldTerminator='''+@fieldTerminator+''',
      rowTerminator='''+@rowTerminator+''' )'
    -- print(@sql)
    exec(@sql)
   
   if(@@error=0)
    insert into mtunes_elog values(getdate(), @cur, 'sp_gameLog_import','success:'+@file) 
   else insert into mtunes_elog values(getdate(), @cur, 'sp_gameLog_import','@@error=:'+cast(@@error as varchar)+':'+@file) 
  end
  else insert into mtunes_elog values(getdate(), @cur, 'sp_gameLog_import','No File:'+@file)   
-- print convert(varchar, @cur, 120)
 set @cur=dateadd(hour, 1, @cur)
end

insert into mtunes_elog values(getdate(), dateadd(hour, -1, @cur), 'sp_gameLog_import','hour import done')

end

GO