I have setup a merge replication and have got a new requirement: The records of a table should not be replicated until the [state] column is changed from 0 to 1. I have created a filter for the replication to limit replication to records with state = 1, but now the records are removed from the subscriber when the state is set to 0. Is it possible to keep those records on the subscriber?
for replicating an specific set of data, you can create a view and replicate it.
there is a small detail though:
All types of replication allow you to replicate views. The view (and its accompanying index, if it is an indexed view) can be copied to the Subscriber, but the base table must also be replicated.
For indexed views, transactional replication also allows you to replicate the indexed view as a table rather than a view, eliminating the need to also replicate the base table. To do this, specify one of the "indexed view logbased" options for the @type parameter of sp_addarticle (Transact-SQL). For more information about using sp_addarticle, see Define an Article.