Sql-server – Appending data to the existing database

sql-server-2012

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?

Best Answer

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

Here's a related article.

Related Solutions

Sql-server – How to configure data collection in SQL Server 2012

It's a bug. This Connect ticket has a workaround for it from Kendra Little:

I was able to work around this issue by temporarily adding a scheduled to the Utility Information collection (@collection_set_id=4) with the SQL command below. After doing this I was able to complete the data collection configuration via the GUI successfully. Afterwards I went back and set the Utility Information collection to manual start, thereby removing the schedule.
declare @schedule_uid uniqueidentifier
select @schedule_uid = schedule_uid from dbo.syscollector_collection_sets where collection_set_id=3

exec dbo.sp_syscollector_update_collection_set
@collection_set_id=4
, @schedule_uid=@schedule_uid

SQL Server 2012 – How to Add a Column to an Existing Table

There is no "BEST" way to add a column. There is only one way - using the T-SQL command. What do you think happens when you use SSMS table design? it's just a way to simplify tasks for those who don't know the syntax. When you click OK, SSMS generates the ALTER TABLE statement and executes it for you.

Best Answer

Related Solutions

Sql-server – How to configure data collection in SQL Server 2012

SQL Server 2012 – How to Add a Column to an Existing Table

Related Question