SQL Server warehouse receiving log

-- Exec sp_gamelog_import getdate (), '2017-03-01'
-- Exec sp_gamelog_import '2017-03-01 ', '2017-03-01' -- fill in data
-- Select * From mtunes_elog order by sendtime DESC, idx DESC, endtime DESC

-- Exec sp_gamelog_import '2017-03-01 ', '2017-03-01'
Create proc sp_gamelog_import (
@ Endtime datetime = NULL, -- logs are generated every hour at 25 minutes. Therefore, data can be completely stored in the database within two hours.
@ Starttime datetime = NULL -- manual entry Note: @ endtime is 2 hours apart
)
/* Storage of game logs by hour
*/
As
Begin
--- One-time batch Import
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) -- manual input is usually included in the confirmed logs, complementing the 2-hour interval
-- Set @ starttime = '2017-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 ')
-- Make sure that the start time is the next hour of the last successful file storage time
-- If @ starttime> (select max (endtime) from mtunes_elog where type = 'SP _ gamelog_import 'and MSG like 'success: e:/cq_policy_log/Action _ % ')
Set @ starttime = (select max (endtime) from mtunes_elog where type = 'SP _ gamelog_import 'and MSG like 'success: e:/cq_policy_log/Action _ % ')
Set @ starttime = dateadd (hour, 1, @ starttime)
End
-- Manually generate and delete the added file records
Else Delete gamelog where aday + ''+ atime between @ starttime and @ endtime

-- Print converter (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_policy_log/Action _'
Set @ fieldterminator = '|'
Set @ rowterminator = char (13) + char (10)

Set @ cur = @ starttime
While @ cur <dateadd (hour,-2, @ endtime) ---- logs are generated at 25 o'clock every hour. Therefore, data can be completely stored in the database within two hours.
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'

-- Determine whether a file exists
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