SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher

Question

Background

I have two instances of SQL server running on two different machines:

One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional.

They both start off with the same snapshot of my replicated database.

I have a utility which runs on the publisher machine which executes the SQL commands against it (all are inserts, updates, deletes). These changes are then pushed out to the subscriber.

I want to know if I am able to TRUNCATE the data in the publisher database so that it has a lower overhead (all I want is the replication ability, I couldn't care less about the actual data in my replication publisher as long as it makes it to the subscribers).

If I do the above and also modify my utility which executes commands to turn INSERTS and UPDATES into a MERGE command, will the data be properly replicated to all subscribers?

---

Example scenario:

The original command that my utility will execute:

UPDATE calendar SET active = 0 WHERE id = 1932

1. The publisher database doesn't have a calendar record with id of 1932, because I truncated it, so an update will affect zero records.

2. But the subscriber database DOES have a calendar record with id 1932.

3. If my utility translates the UPDATE above into a MERGE, will the change be pushed out to the subscriber database even though it executes as an INSERT on the publisher, but an UPDATE on the subscriber?

---

Thanks in advance.

Answer 1

If you truncated the table on the publisher the record in the transaction log would be an insert, and therefor an insert would be executed against the subscriber.

Now what you can do is modify the stored procedures that replication uses at the subscriber so that when the insert procedure fires it first looks to see if the record exists and if it does it does an update, and if it doesn't it does an insert.