How to insert new table with JSON data through API in Azure SQL Database

Question

I have the following problem. We are using Azure SQL Database for the Data processing. Instead of wizard import every time we would like to automatically load the data through API from our accounting platforms. (API Documentation Links: https://hellocashapi.docs.apiary.io/#introduction/authentication , https://www.zoho.com/books/api/v3/)

Basically my task is to get the data through API from these platforms and create the table in our Azure SQL Database and insert this data therein.

Can anyone recommend me the platform to resolve this issue? or please send me the link with the documentation which would show me the way to do that.

Thank you.

Alberto Morillo · Accepted Answer · 2019-12-20 20:04:35Z

1

If you can put the JSON on a SQL variable like this

DECLARE @json NVARCHAR(MAX) = N'[  
  {  
    "Order": {  
      "Number":"SO43659",  
      "Date":"2011-05-31T00:00:00"  
    },  
    "AccountNumber":"AW29825",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":1  
    }  
  },  
  {  
    "Order": {  
      "Number":"SO43661",  
      "Date":"2011-06-01T00:00:00"  
    },  
    "AccountNumber":"AW73565",  
    "Item": {  
      "Price":2024.9940,  
      "Quantity":3  
    }  
  }
]

Then you can create a table using the WITH clause

SELECT * INTO TableName1
FROM OPENJSON ( @json )  
WITH (   
              Number   varchar(200)   '$.Order.Number',  
              Date     datetime       '$.Order.Date',  
              Customer varchar(200)   '$.AccountNumber',  
              Quantity int            '$.Item.Quantity',  
              [Order]  nvarchar(MAX)  AS JSON  
 )

edited Dec 20, 2019 at 20:04

answered Dec 20, 2019 at 19:10

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Leon Yue · Accepted Answer · 2019-12-26 02:08:41Z

Firstly, not all the API is supported as Source data in Data Factory.

Please reference this document: Azure Data Factory connector overview

Data Factory doesn't support hellocashAPI. That means do that with Data Factory.

Secondly, Data Factory now support supports creating a destination table automatically. Referecec:Copy Activity in Azure Data Factory supports creating a destination table automatically.

Summary:

Load data faster with new support from the Copy Activity feature of Azure Data Factory. Now, if you’re trying to copy data from an any supported source into SQL database/data warehouse and find that the destination table doesn’t 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

Hope this helps.

Collectives™ on Stack Overflow

How to insert new table with JSON data through API in Azure SQL Database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related