Create a target table on-the-fly in Data Factory

Michael Shparber 141 Reputation points

I am new to Data Factory.
I want to copy data from REST API into SQL Server tables.
Is there a way to automatically create SQL tables with data types based on the API calls?
I don't want to do this manually.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,751 questions
0 comments No comments
{count} votes

Accepted answer
  1. ChiragMishra-MSFT 956 Reputation points

    Hi @Michael Shparber ,

    You can Load data faster with new support from the Copy Activity feature of Azure Data Factory. Now, if you are trying to copy data from an any supported source into SQL database/data warehouse and find that the destination table does not exist, Copy Activity will create it automatically. After the data ingestion, review and adjust the sink table schema as needed.

    This feature is supported with:

    • Azure SQL Database
    • Azure SQL Database Managed Instance
    • Azure SQL Data Warehouse
    • SQL Server

    To automatically create a destination table, follow this path: ADF authoring UI > Copy activity sink > Table option > Auto create table. Or, click on the “tableOption” property in the Copy Activity sink payload. Please refer to the below screenshot for details :


    Hope this helps. Stay safe !

    2 people found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Bobby Lansing 21 Reputation points

    When adding a SQL database it requires a table to be selected. Even with "Auto create table" selected I don't see data coming into the selected table or a new table being created.

    How do I ensure that this creates a new table the first time it is run?

    4 people found this answer helpful.
    0 comments No comments

  2. Michael Shparber 141 Reputation points

    Awesome! Thanks @ChiragMishraMSFT-1092

    1 person found this answer helpful.
    0 comments No comments

  3. Michael Shparber 141 Reputation points

    I don't think it allows to Auto-create a table when the source is REST API

    0 comments No comments

  4. Karthik Bhyresh 1 Reputation point

    I added a dummy table name in SQL Dataset by clicking on edit check box - dbo.TestEmployee

    and then copy activity ran fine, it create a table with above name and inserted data.

    Before: 118604-image.png

    After: 118622-image.png