Updating Oracle on-premise table from a Synapse table

pmscorca 877 Reputation points
2024-06-01T12:00:58.09+00:00

Hi,

I need to update an Oracle on-premise table from a Synapse table by joining the Oracle table with the Synapse table avoiding to build a staging table on Oracle platform to copy the Synapse data. It is a problem to obtain the authorization from my customer to create an Oracle staging table.

Any suggests to me to solve this issue? Thanks

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,631 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,029 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 18,741 Reputation points
    2024-06-01T17:20:04.4833333+00:00

    You need to create 2 linked services for Oracle and Synapse and configure the connection properties (server name, database name, username, password...).

    Next, create datasets referencing the tables in the Oracle and Synapse databases where you specify the table or use a query to define the dataset.

    Then, you can use Data Flows to perform the following :

    • Add a source transformation to read data from the Synapse table dataset.
    • Add a Join transformation to join the data from the Synapse table with the Oracle table.
    • Add an Alter Row transformation to specify the update conditions to determine which rows should be updated in the Oracle table.
    • Add a Sink transformation to return the updated data to the Oracle table.
    • Configure the Sink to perform an update operation. You can use the Upsert setting if applicable.
    1 person found this answer helpful.