TQL statement for inserting json integer array to table rows

Question

How do I insert a JSON integer array to a table column in SQL server. Suppose I have the following json variable:

declare @jsonArray as varchar(max);
set @jsonArray = '[1,2,3,4]';

How do I insert the integer values into a table's integer type column using some of the SQL server JSON statements/functions?

Pawan Kumar · Accepted Answer · 2018-02-12 17:57:46Z

2

You can use openjson to split the JSON data-

declare @jsonArray as varchar(max);
set @jsonArray = '[1,2,3,4]';
SELECT Value FROM OPENJSON(@jsonArray)

Value
-----------
1
2
3
4

(4 rows affected)

Please use like below

declare @jsonArray as varchar(max);
set @jsonArray = '[1,2,3,4]';
INSERT INTO yourTableName(ColumnName)
SELECT Value FROM OPENJSON(@jsonArray)

Read more from - https://msbiskills.com/2018/01/22/new-t-sql-features-in-sql-server-2016-xii-openjson-function-sql-server/

answered Feb 12, 2018 at 17:57

Pawan Kumar

2,02112 silver badges12 bronze badges

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

1 Comment

Aamir Over a year ago

Thank you! I had seen some very complex json object examples, but didn't find anything simple as an integer array. Also didn't know about the "Value" keyword. That's something new for me!

Collectives™ on Stack Overflow

TQL statement for inserting json integer array to table rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related