How to merge new data inserted and updated in SQL Server

SSIS General How To
, , ,
1

Introduction

Integrating new data and updates into an existing SQL Server table is crucial in ETL workflows. This ensures your target table stays current with the latest data while avoiding duplicates or loss. SQL Server offers the powerful Merge transformation component to achieve this efficiently.

In this article, we will walk through a step-by-step guide to using the Merge component in SSIS to streamline your data integration tasks with our Upsert destination component.

Prerequisites

Before starting, ensure you have the following:

Step-by-Step Guide

Step 1: Set Up Your Source Tables

You need two source tables to begin with:

  1. SourceTable: Contains the latest data, including new and updated rows.
  2. TargetTable: The existing data must be updated or supplemented with new rows.

This tutorial will use a ZappySys JSON Source for the latest data and an OLE DB Source for the existing data.

Step 2: Use the Sort Component

The Sort component ensures that data is sorted before merging, which is critical for the Merge transformation to function correctly.

  1. correctlyigure Sorting**:
    • Specify the columns to be sorted and their sort order.
    • Enable the Remove duplicates option to avoid redundant rows.
      Note: Avoid using the Sort component for large datasets since it loads all data into memory, which may impact performance.
      Sort Component Configuration
      Sort Component Configuration671×637 13.8 KB

Step 3: Configure the Merge Component

Once both datasets are sorted, connect them to the Merge component:

  1. Link the Sort Output from SourceTable and TargetTable to the Merge component.
  2. Define the columns to merge by mapping them in the Merge configuration window.
    Merge Component Configuration
    Merge Component Configuration753×293 8.33 KB

Step 4: Add a Destination Component

Finally, use a destination component to update or insert the merged data into your SQL Server database:

  • Drag and drop an OLE DB Destination component into your Data Flow Task.
  • Map the output of the Merge component to the OLE DB Destination and configure it to point to your database table.
    Example result

Step 5: A more manageable and better option, the Upsert Destination Component

The better way to do this is to use the Upsert Destination component. This component lets you preview data from the database, select the options to insert or/and update, and more.

  1. Create your SQL connection, the table, map the columns and check the column you want for the update/insert

  2. Go to the Advanced tab for more options if it needs it

    Upsert destination example
    Upsert destination example958×645 48.9 KB

  3. Run the package, and you will see the result
    Example result 2

Conclusion

Merging new and updated data in SQL Server using the Merge component efficiently maintains synchronized datasets. By leveraging tools like the ZappySys JSON Source and the Upsert Destination in your SSIS package, you can streamline your ETL workflows while ensuring data accuracy.

Follow the steps in this guide to implement the Merge transformation in your projects and optimize your SQL Server integration tasks.

For more advanced components and features, explore the full capabilities of the ZappySys SSIS PowerPack.

References