Lookup Pattern: Upsert
Published Mar 25 2019 02:16 PM 738 Views
Copper Contributor
First published on MSDN on Nov 22, 2008

This is a pretty basic pattern where we use a lookup to determine whether we need to update and existing row, or insert a new one. The lookup checks if a key or set of values exists. If the key isn't found, the row is sent to an OLEDB Destination for the insert. If it is found, it is sent to an OLEDB Command to do the update.

Note, the OLEDB Command transform operates on a row by row basis - so a separate SQL statement will be executed for every row going in. As such, the OLEDB Command can be very slow if you're processing a large number of rows. An alternate approach is to stage the data, and either update your target table using the MERGE statement , or an UPDATE ... FROM batch command.

You can also check out the MERGE Destination or Batch Destination available on Codeplex. John Welch (author of the Batch Destination) has a blog post which compares the two.

Version history
Last update:
‎Mar 25 2019 02:16 PM
Updated by: