Consider the following code
SELECT 1 UNINO SELECT 2 as no
Note that UNINO is the misspelled UNION. How does the above code work without any error and what is the result?
Consider the following code
SELECT 1 UNINO SELECT 2 as no
Note that UNINO is the misspelled UNION. How does the above code work without any error and what is the result?
In Part 1 of this series, it was shown how to generate JSON data from the existing table. In this post we can explore how to import JSON data into a table
SQL Server 2016 supports OPENJSON, a table-value function that parses the JSON data and return the column names and their values as key and value columns
Simple example is
select * FROM OPENJSON( '{ "emp_name": "Madhivanan", "dob": "2000-10-19" } ')
The result is
key value type ------------- ----------------- ---------- emp_name Madhivanan 1 dob 2000-10-19 1
If you want to get the values not in key/value pairs but in normalised structure (column and rows), you can specify the schema using WITH option as shown below
select * FROM OPENJSON( ' { "emp_name": "Madhivanan", "dob": "2000-10-19" } ') with ([emp_name] varchar(100),[dob] date)
The result is
emp_name dob -------------- ----------- Madhivanan 2000-10-19
The above works well for single JSON array. If there are multiple arrays, you need to specify the root element to get all of them.
Let us consider the following example
Create a new table
create table #emp(emp_name varchar(100), dob date)
Now the following code will load the JSON data into this table
DECLARE @json NVARCHAR(MAX) = N' { "Employee": [{ "emp_name": "Madhivanan", "dob": "2000-10-19" }, { "emp_name": "Saran", "dob": "1998-04-22" }, { "emp_name": "Mugil", "dob": "1978-02-14" }] }' create table #emp(emp_name varchar(100), dob date) Insert into #emp(emp_name,dob) select * from OPENJSON(@json,'$.Employee') with ([emp_name] varchar(100),[dob] date) select * from #emp
The result is
emp_name dob ---------------- -------------- Madhivanan 2000-10-19 Saran 1998-04-22 Mugil 1978-02-14
So this way you get JSON data in a structure format and then import them to a table
Other posts on new features of SQL Server 2016
Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT
SQL Server 2016 – Drop If Exists
Exploring SQL server 2016 T-SQL functions- COMPRESS
Exploring SQL server 2016 T-SQL functions- DECOMPRESS
Exploring SQL Server 2016 – JSON data – Part 1
Now-a-days JSON (JavaScript Object Notation) data format has become popular in terms of exchanging data to different data sources. It is the primary format in storing data for NoSQL databases like Azure DocumentDB, MongoDB, etc. It is easily in readable format and can store unstructured data as well.
You can parse and generate data in JSON format in SQL Server from the version 2016 onwards. In this post we will explore how to generate data in simple JSON format.
Let us create the following dataset
create table #emp(emp_name varchar(100), dob date) insert into #emp(emp_name,dob) select 'Madhivanan','2000-10-19' union all select 'Saran','1998-04-22' union all select 'Mugil','1978-02-14'
Now you can use JSON AUTO option to generate data in json format as show below
select * from #emp for json auto
The result is
JSON_F52E2B61-18A1-11d1-B105-00805F49916B [{ "emp_name": "Madhivanan", "dob": "2000-10-19" }, { "emp_name": "Saran", "dob": "1998-04-22" }, { "emp_name": "Mugil", "dob": "1978-02-14" }]
As you can see, each column is by default separated by colon and each row is separated by comma and all values are within double quotes.
If you want to add single top level element on the top say “Employee”, that can be done using ROOT option as shown below
select * from #emp for json auto, root('Employee')
The result is
JSON_F52E2B61-18A1-11d1-B105-00805F49916B { "Employee": [{ "emp_name": "Madhivanan", "dob": "2000-10-19" }, { "emp_name": "Saran", "dob": "1998-04-22" }, { "emp_name": "Mugil", "dob": "1978-02-14" }] }
If you want to have a column alias you can do like below
select (select * from #emp for json auto, root('Employee')) as test_json
The result is
test_json { "Employee": [{ "emp_name": "Madhivanan", "dob": "2000-10-19" }, { "emp_name": "Saran", "dob": "1998-04-22" }, { "emp_name": "Mugil", "dob": "1978-02-14" }] }