0

I've got a quick question , that would be great if anyone can help me with this problem: I have 2 SQL 2012 databases A & B , they are both Identical same tables and same structures as well. Data is inserted into DB A and on regular schedule I need the data to be inserted into DB B automatically and APPENDS to the existing data in DB B,after that, data in DB A gonna deleted and the whole process starts over again with new records.I couldn't preform this action using any methods of replication because once the data is deleted from DB A same thing gonna happen in DB B. On the other hand these data contains FK pointing to other tables in the database so if the first problem solves and it starts APPENDING data to the existing data on DB B all these FK should be updated with new values in DB B. Do you guys have any solution using SQL itself or any other tools?

  • 1
    Are these databases on the same server or separate ones? – LowlyDBA Sep 14 '16 at 19:58
3

You can use Transactional Replication, but you have to exclude the DELETEs. Just replicate INSERTs and UPDATEs.

Here's a related article.

0

We had a similar issue. I created procedure spExportAuditLog to resolve this issue. It can be run as often as desired, for us, to export data to database B from A--which is on a different server.

The biggest concern for us was borging production database A by performing too much I/O on it all at once. So we chunked the records a batch at a time and used waitfor delay '000:00:10' to pause this process to allow other activity to occur.

Also, we absolutely wanted a complete rollback should any issues occur. Hope this helps you roll your own. Also, I detest cursors so I created my own while/end loop instead.

We absolutely did NOT want the hassle of replication. Hope this helps you roll your own!

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 [<your linked server here>].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 [<your linked server here>].audit.dbo.auditlog(X1, X2, X3, X4, X5, X6, X7, X8, X9)
        select X1, X2, X3, X4, X5, X6, X7, X8, X9
                     from newaudit.dbo.auditlog where X1 between @watermark and @watermark+@batch and X1<= @max order by id
                     if @@ERROR <> 0 select @errorvar=@errorvar+1
        delete from newaudit.dbo.auditlog where X1between @watermark and @watermark+@batch and X1<= @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 [<your linked server here>].audit.dbo.auditlog
print @AuditCounter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.