Share via

Dynamically update schema from source to destination sql tables

sam nick 306 Reputation points
2021-04-28T00:56:40.533+00:00

Hello,
I have a requirement to incremental load into my Azure SQL tables (destination) from my source which are also Azure SQL tables, within the same server but different databases. I thought I could get this done by ADF without the use of stored procedures. But looks like i have to use stored procedures. And the only way to accomplish this is via Elastic queries. Now i have around 60 tables in each DB. Does this mean that i have to create 60 external tables in addition to have the same 60 tables in the database , just to allow two DB's to talk to each other.

Does anyone have any recommendations on how to achieve this, without dropping and recreating the tables everytime.

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,740 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anurag Sharma 17,606 Reputation points
    2021-04-28T06:14:02.26+00:00

    Hi @sam nick , welcome to Microsoft QNA forum.

    I assume we are talking about just the data load here. We can do incremental load through ADF without creating External tables as well. We need to create/design a new table that would hold reference data for every table about last inserted/updated/deleted record (could be last modified date or ID). As an example if we have 50 tables, this new table will have 2 columns and 50 rows. First column will have the name of table and second column will have the last modified date or ID up to which we have already loaded the data. Our ADF will take this table as reference and pick the records that are greater than this value and load into the other database.

    Below is one example. In this example, source is on-premise SQL Server but we can use Azure SQL Database as well:

    Incrementally load data from multiple tables in SQL Server to a database in Azure SQL Database using the Azure portal

    Please let us know if this helps.

    ----------

    If answer helps, please mark it 'Accept Answer'

  2. KalyanChanumolu-MSFT 8,336 Reputation points
    2021-05-06T11:22:59.1+00:00

    @sam nick Apologies for the delay in getting back to you.
    Please check this video for handling evolving schemas with ADF.

    ----------

    If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.