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?
You can use Transactional Replication, but you have to exclude the DELETEs. Just replicate INSERTs and UPDATEs.
Here's a related article.
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