Create proc [dbo].[spExportAuditLog]
as

    set nocount on
    declare @errorvar int,@max int, @min int, @watermark int, @batch int, @beforecounter int, @aftercounter int, @AuditCounter int, @NewAuditCounter int, @errortext varchar(255)
    select @beforecounter=0
    select @aftercounter=0
    select @NewAuditCounter = count(*) from newaudit.dbo.auditlog
    print 'NewAudit AuditLog Count Before'
    print @NewAuditCounter
    print 'Audit AuditLog Count Before'
    select @AuditCounter=count(*) from [TARGETSQLSERVER].audit.dbo.auditlog
    print @AuditCounter

    select top 1 @max=ID from newaudit.dbo.auditlog where  datediff( minute, datestamp,getdate()) <= 1440 order by ID

    select @min=min(ID) from newaudit.dbo.auditlog
    Set XACT_ABORT on
    select @batch =100000
    select @watermark=@min
    while (@watermark <=@max)
    begin
            begin transaction
            insert into [TARGETSQLSERVER].audit.dbo.auditlog(ApplicationName, MessageType, Message, Comments, UserID, CustomFields, Datestamp, ServerName, MRN)
            select ApplicationName, MessageType, Message, Comments, UserID, CustomFields, Datestamp, ServerName, MRN
                         from newaudit.dbo.auditlog where ID between @watermark and @watermark+@batch and ID <= @max order by id
                         if @@ERROR <> 0 select @errorvar=@errorvar+1
            delete from newaudit.dbo.auditlog where ID between @watermark and @watermark+@batch and ID <= @max
            if @@ERROR <> 0 select @errorvar=@errorvar+1
            if @errorvar > 0
            begin
                select @errortext= 'ROLLBACK!! Problem with error IDs between:' + CONVERT(varchar(50), @watermark) + ' and ' +CONVERT(varchar(50), @watermark+@batch)
                print @errorText
                rollback
                select @errorvar=0
            end
            else commit
            select @watermark = @watermark+@batch +1
            waitfor delay '000:00:10'
    end

    select @NewAuditCounter = count(*) from newaudit.dbo.auditlog
    print 'NewAudit AuditLog  Count After'
    print @NewAuditCounter
    print 'Audit AuditLog Count After'
    select @AuditCounter=count(*) from [TARGETSQLSERVER].audit.dbo.auditlog
    print @AuditCounter