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