Upsert destination

As explained in 100 SQL Server Mistakes and How to Avoid Them

An upsert destination is a data-flow component that inserts rows absent from a target and updates rows that already exist.

An upsert destination inserts rows whose key combination is absent from a target and updates aggregated values when that combination already exists. section 6.3.2

Merge marketing aggregates

A data flow reads marketing.Impressions, rolls EventTime to dates, calculates average BidPrice and CostPerMille, then upserts reporting.ImpressionAggregates using its key combination. Compared with one Execute T-SQL Statement task, the flow can redirect failed rows individually and make transformation logic visible. section 6.3.2

Figure 6.12 Upsert destination—Column Mappings tab

Use the column-mappings view to inspect how incoming aggregate columns map to the upsert target. section 6.3.2

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains the insert-or-update rule and places it within a complete source, transformation, aggregation, and destination flow.

Examples

Uses marketing.Impressions and reporting.ImpressionAggregates in a marketing-impressions aggregation scenario.

What it assumes

Assumes familiarity with SSIS data-flow sources and transformations, plus target keys and aggregate values.

What you can do after

Apply it via SSIS to load aggregate results while redirecting failed rows and observing flow errors or slow performance.

How it connects

Connects data flow sources, derived column transformations, aggregate transformations, and Execute T-SQL Statement tasks.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

• business keyconfigured by · A business key is one or more domain-meaningful properties whose values identify a business entity or distinguish it for matching and uniqueness.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.