Use CardioLog
GO

--================================
-- archive Events
--================================
declare @cuttingEventId bigint 
declare @batchSize	int = 1000
declare @rowCount   int = 1
declare @totalRows  int = 0
declare @cuttingDate datetime    

-- Edit start date for archiving, format is YYYYMMDD
-- The script will archive events with timestamp < @cuttingDate 

set @cuttingDate = 'YYYY-MM-DD' -- YYYY-MM-DD usage data earlier that this date will be archived for example '2017-01-01' will archive any data before 2017.

select top 1 @cuttingEventId = EventLogId from tab_event_log with(nolock)
where Timestamp < @cuttingDate
order by Timestamp desc

print @cuttingEventId

set nocount on 

begin try
	
	while Exists (select 1 from tab_event_log with(nolock) where timestamp < @cuttingDate )
	begin
		begin tran
		
		-- delete a batch from tab_event_log_archive

		delete  tab_event_log_users
		OUTPUT DELETED.* 
		into tab_event_log_users_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId 

		-- delete a batch from tab_event_log_archive
		delete tab_event_log
		OUTPUT DELETED.EventLogId, DELETED.EntityId, DELETED.EntityType, DELETED.EventType, DELETED.Url, DELETED.UserName, DELETED.SessionId, DELETED.Timestamp,
		DELETED.Param1, DELETED.Param2, DELETED.Param3, DELETED.QueryString, DELETED.Created,
		DELETED.FromEventId, DELETED.SPLocation, DELETED.flyweightId, DELETED.ToEventId, DELETED.CategoryFlyweightId, DELETED.browserType, DELETED.clientIP, DELETED.SearchURL, DELETED.WorkStationIP
		into tab_event_log_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId
				

		delete tab_geo_log
		OUTPUT DELETED.EventLogId, DELETED.countryCode, DELETED.userName, DELETED.timestamp, DELETED.sessionId, 
		DELETED.workStationId,  DELETED.region, DELETED.city, DELETED.categoryCountryCode,
		DELETED.categoryRegion, DELETED.categoryCity
		into tab_geo_log_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId 
		
		delete tab_goal_test_log
		OUTPUT DELETED.* 
		into tab_goal_test_log_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId 
		
		delete tab_test_log
		OUTPUT DELETED.* 
		into tab_test_log_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId 
		
		delete tab_referrer_log
		OUTPUT DELETED.*
		into tab_referrer_log_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId 
		
		delete tab_goal_log
		OUTPUT DELETED.* 
		into tab_goal_log_archive
		where eventlogid between @cuttingEventId - @batchSize and @cuttingEventId 
		
		set @cuttingEventId = @cuttingEventId - @batchSize
		commit tran
		
end


end try

begin catch

	/*alter table tab_event_log_users
		check constraint FK_tab_event_log_tab_event_log_users
	*/
	
	declare 
        @errormessage    nvarchar(4000),
        @errornumber     int,
        @errorseverity   int,
        @errorstate      int,
        @errorline       int,
        @errorprocedure  nvarchar(200);

	select 
        @errornumber = error_number(),
        @errorseverity = error_severity(),
        @errorstate = error_state(),
        @errorline = error_line(),
        @errorprocedure = isnull(error_procedure(), '-');

		select @errormessage = N'error %d, level %d, state %d, procedure %s, line %d, ' + 
            'message: '+ error_message();

	if (xact_state()) <> 0
    begin
        rollback tran
    end

	raiserror 
        (
        @errormessage, 
        @errorseverity, 
        1,               
        @errornumber,
        @errorseverity,
        @errorstate,
        @errorprocedure,
        @errorline
        );
end catch