Search code examples
azurecopyazure-sql-databaseetlazure-data-factory

Azure Data factory - insert a row in the azure sql database only if it doesn't exist

I have an excel file as source that needs to be copied into the Azure SQL database using Azure Data Factory.

The ADF pipeline needs to copy the rows from the excel source to SQL database only if it is already not existing in the database. If it exists in the SQL database then no action needs to be taken.

looking forward to the best optimized solution.

Solution

  • You can achieve it using Azure data factory data flow by joining source and sink data and filter the new insert rows to insert if the row does not exist in the sink database.

    Example:

    1. Connect excel source to source transformation in the data flow.

    enter image description here

    Source preview:

    enter image description here

    1. You can transform the source data if required using the derived column transformation. This is optional.

    2. Add another source transformation and connect it with the sink dataset (Azure SQL database). Here in the Source option, you can select a table if you are comparing all columns of the sink dataset with the source dataset, or you can select query and write the query to select only matching columns.

    enter image description here enter image description here

    Source2 output:

    enter image description here

    1. Join source1 and source2 transformations using the Join transformation with join type as Left outer join and add the Join conditions based on the requirement.

    enter image description here

    Join output:

    enter image description here

    1. Using filter transformation, filter out the existing rows from the join output.

    Filter condition: isNull(source2@Id)==true()

    enter image description here

    Filter output:

    enter image description here

    1. Using the Select transformation, you can remove the duplicate columns (like source2 columns) from the list. You can also do this in sink mapping by editing manually and deleting the duplicate rows.

    enter image description here

    1. Add sink and connect to sink dataset (azure SQL database) to get the required output.

    enter image description here