Morning all,

I think the way that the MERGE source table has been written would definitely under perform compared to the upsert.

e.g.

-- MERGE

MERGE #Target t

USING #Source s

ON s.ID = t.ID

WHEN MATCHED

THEN UPDATE

SET Value = s.Value

WHEN NOT MATCHED

THEN INSERT

( ID, Value )

VALUES

( s.ID

, s.Value

);

This can be wrote as:-

-- MERGE

MERGE #Target t

USING (@ID, @Value

) s (ID, Value)

ON s.ID = t.ID

WHEN MATCHED

THEN UPDATE

SET Value = s.Value

WHEN NOT MATCHED

THEN INSERT

( ID, Value )

VALUES

( s.ID

, s.Value

);

This would out perform any UPSERT as you are basically creating the source table in-memory which will not touch disks, thus the comparison is done in memory.

you just need to work out how to get the @id and @value variables set.

Thanks

Andrew