Question 192591 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Insert JSON Data into already existing SQL Table

I am doing an API call which returns the following JSON as a string:

[{
    "id": 103000180455,
    "name": "Personal",
    "created_at": "2023-06-12T07:12:52Z",
    "updated_at": "2023-06-12T07:12:52Z",
    "personal": true,
    "responses_count": 1
}, {
    "id": 103000180454,
    "name": "General",
    "created_at": "2023-06-12T07:12:52Z",
    "updated_at": "2023-06-12T07:12:52Z",
    "personal": false,
    "responses_count": 2
}, {
    "id": 103000181609,
    "name": "Custom",
    "created_at": "2023-06-14T15:51:01Z",
    "updated_at": "2023-06-14T15:51:01Z",
    "personal": false,
    "responses_count": 3
}]

What I want to do, is insert all of these into an already existing SQL Table.
My table consists of columns which already exist in the JSON.
eg.
ID
Name
Created_At etc. etc.

id name created_at updated_at personal responses_count
103000180455 Personal 2023-06-12T07:12:52Z 2023-06-12T07:12:52Z true 1
103000180454 General 2023-06-12T07:12:52Z 2023-06-12T07:12:52Z false 2
103000181609 Custom 2023-06-12T07:12:52Z 2023-06-12T07:12:52Z false 3

What I also want to do, is if the ID already exists, is to update the fields, rather than insert new ones. As the API call could bring back more objects if that makes sense?

Any help would be great, Cheers! 🙂

I have tried multiple tutorials online, but all of which create the table for you, or inserts the data manually. As I am having an automated process, this won’t work.

Tags:

2

Answers


  1. 0

    You can try this :

    DECLARE @jsonData NVARCHAR(MAX) = '[{
    "id": 103000180455,
    "name": "Personal",
    "created_at": "2023-06-12T07:12:52Z",
    "updated_at": "2023-06-12T07:12:52Z",
    "personal": 1,
    "responses_count": 1
}, {
    "id": 103000180454,
    "name": "General",
    "created_at": "2023-06-12T07:12:52Z",
    "updated_at": "2023-06-12T07:12:52Z",
    "personal": 0,
    "responses_count": 2
}, {
    "id": 103000181609,
    "name": "Custom",
    "created_at": "2023-06-14T15:51:01Z",
    "updated_at": "2023-06-14T15:51:01Z",
    "personal": 0,
    "responses_count": 3
}]';

-- Insert or update data from JSON
MERGE your_table AS target
USING (
    SELECT  JSON_VALUE(j.item, '$.id') AS id,
            JSON_VALUE(j.item, '$.name') AS name,
            JSON_VALUE(j.item, '$.created_at') AS created_at,
            JSON_VALUE(j.item, '$.updated_at') AS updated_at,
            JSON_VALUE(j.item, '$.personal') AS personal,
            JSON_VALUE(j.item, '$.responses_count') AS responses_count
    FROM OPENJSON(@jsonData) AS j
) AS source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name,
               target.created_at = source.created_at,
               target.updated_at = source.updated_at,
               target.personal = source.personal,
               target.responses_count = source.responses_count
WHEN NOT MATCHED THEN
    INSERT (id, name, created_at, updated_at, personal, responses_count)
    VALUES (source.id, source.name, source.created_at, source.updated_at, source.personal, source.responses_count);

    MERGE (Transact-SQL)

    This documentation provides detailed explanations, examples, and syntax for using the MERGE statement to perform insert, update, or delete operations based on data from a source table or query. The provided example in my response demonstrates the usage of MERGE with JSON data.

    Login or Signup to reply.
  2. 0

    Use OPENJSON to convert JSON to a rowset :

    SELECT *
FROM OPENJSON(@json) WITH (
    id BIGINT 'strict $.id',
    name VARCHAR(20) '$.name',
    created_at DATETIME2 '$.created_at',
    updated_at DATETIME2 '$.updated_at',
    personal bit '$.personal',
    responses_count int '$.responses_count'
)

    Result :

    id name created_at updated_at personal responses_count
    103000180455 Personal 2023-06-12 07:12:52.0000000 2023-06-12 07:12:52.0000000 True 1
    103000180454 General 2023-06-12 07:12:52.0000000 2023-06-12 07:12:52.0000000 False 2
    103000181609 Custom 2023-06-14 15:51:01.0000000 2023-06-14 15:51:01.0000000 False 3

    Demo here

    This can then be used as source of a merge query .

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search