locked
Update SQL DB through Azure Data Factory RRS feed

  • Question

  • I am trying to update a sql table from the source file based on the indicator field.

    If the indicator is I i need to Insert and if it is U I need to update.

    For Inserting the data I am using copy data and for update I am using stored procedure. 

    I want to know if there is any other way to update a sql db in datafactory. Why is there no upsert option as it comes with Cosmos DB.

    Also if the only possible way to update sql db is through stored procedure then why is it so?

    Thanks,

    Sweta



    • Edited by sahusweta Tuesday, June 11, 2019 5:59 PM
    Tuesday, June 11, 2019 5:52 PM

Answers

All replies

  • You can update rows in SQL DB using ADF's Mapping Data Flows with the Alter Row transformation:

    https://docs.microsoft.com/en-us/azure/data-factory/data-flow-alter-row

    Wednesday, June 12, 2019 11:38 PM
  • Hello Sweta, and thank you for your inquiry.

    The difference between Lookup activity and Stored Procedure activity, is that the Lookup is expected to return a result, where the Stored Procedure is not required to.

    This means it is possible to write to a table using a lookup, as long as a result is returned.  I.e. for ta table with (ID, FirstName, LastName):

    BEGIN
    INSERT INTO db.mytable (FirstName, LastName)
    VALUES ("Sweta","Sweta");
    SELECT MAX (ID)
    FROM db.mytable;
    END

    This would insert a row, and (assuming autoincrement) returns the ID.

    Thursday, June 13, 2019 12:02 AM
  • Thanks a lot for the details. Seems like we can do a lot more transformations in Data Flow. 

    Thanks, Sweta

    Thursday, June 13, 2019 3:46 PM
  • Thanks for the details

    Thanks, Sweta

    Thursday, June 13, 2019 3:46 PM