How to replicate only INSERT and UPDATE Statements

Question

How to replicate only INSERT and UPDATE Statements

Andrey-320088

SSC Enthusiast

Points: 147
More actions
May 16, 2006 at 10:16 am

#91737

Hi,
What is the simplest way with transactional replication to replicate only INSERT and UPDATE Statements?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Disable the Delete function. Assuming you're on 2000, go into the publication properties, then articles. Click the box to the right of the article, then pick the "Commands" tab. Uncheck the "Replace DELETE commands..." box. I've installed the 2005 client and like to use that to manage my 2000 servers to get used to it. Disabling DELETEs with 2005 is a bit more "English".

Why do you want to disable Deletes ?

Disabling DELETEs may give you unexpected results though. For instance if the primary key is UPDATED at the publisher, SQL really does a DELETE and INSERT to accomplish that, so at the subscriber, you'll have the old record and the new record. Also, if you delete a record at the publisher, but not at the subscriber, then at a later date it gets re-inserted at the publisher, you may encounter a situation where you're trying to insert a duplicate key at the subscriber.

Set up a little test scenario and try it out. That's what I just did ... Took about 5 minutes.

Andrey-320088 SSC Enthusiast Points: 147 More actions · Answer 2

Cool! Thank you very much homebrew01 . MS hid this option well .

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Another "gotcha" could be IF you reinitialize the subscription for some reason. Then when the data from the publisher is re-snapshotted and sent to the subscriber, it will wipe out the current subscriber data, and then replace it with the data WITHOUT the deleted records. Depending on their importance, it might be a good idea to archive the deleted records somewhere just in case.

Andrey-320088 SSC Enthusiast Points: 147 More actions · Answer 4

Yeah, I'm kind of finding it out that it's very easy to wipe out my archived data if replication is reinitialized. Looks like it easier in this case to use DTS or just a plain condtional select, copy and insert. I'm going to go with the last one. Anyway, thanks for your help.