SQL Server merge statement source and target issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise and using the new Merge statement. From my experiment, I find source is always read only (table content not modified, i.e. no record is deleted/inserted/updated)? Is that correct understanding?

thanks in advance, George

Answer 1

Yes you're understanding is correct. You have a source and a target, and you check for each row in the source whether or not it exists in the target.

What gets updated is always the target - depending on your setup, you can insert new row, update or delete existing ones - whichever you choose.

But the source never gets touched - only read. That is the case, and it's by design (and it's a good thing, IMHO).