How to insert json data directly into table in sql.

-
Let us consider the below JSON as sample data,

ex: [{
"info": [{
"id": 1,
"name": "John",
"surname": "Smith",
"age": 25
},
{
"id": 2,
"name": "Abijal",
"surname": "Smith",
"age": 45
},
{
"id": 3,
"name": "Abhijit",
"surname": "singh",
"age": 41
}]
}]

To insert the above JSON we can use the below query,

Query:

declare @json varchar(max) =' [{ "info": [{ "id": 1, "name": "John", "surname": "Smith", "age": 25 }, { "id": 2, "name": "Abijal", "surname": "Smith", "age": 45 }, { "id": 3, "name": "Abhijit", "surname": "singh", "age": 41 }] }] ' Insert into EmployeeDetails SELECT Id,Age,FirstName,LastName from( SELECT * FROM OpenJson(@json)
WITH (inffo NVARCHAR(MAX) '$.info' AS JSON) OUTER APPLY OpenJson(inffo) WITH ( Id VARCHAR(30) '$.id',FirstName VARCHAR(30) '$.name',LastName VARCHAR(30) '$.surname',Age VARCHAR(30) '$.age') ) as empdetails

Popular posts from this blog

How to get the user details, user current level, next level, Rank in sql server

-
 ALTER PROCEDURE [dbo].[SP_GETUSERPROFILE] @UserId int AS BEGIN --checks whether user reported any tweet or not IF EXISTS(SELECT * FROM OnGoingReports WHERE UserId=@UserId) BEGIN --if user already reported tweets then gets the user details and rank select UserId,UserName,Name,UserReportedTweetCount,Rank      from  (select UserId,UserName,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from      (select ogr.UserId,uc.UserName,Name,Count(*) UserReportedTweetCount from UserCredentials uc       inner join OnGoingReports ogr       on       ogr.UserId=uc.UserId        GROUP BY ogr.UserId,uc.Name,uc.UserName) as TopList ) as UserRank      where UserId=@UserId END ELSE BEGIN --if user not reported any tweets then it get the default value as 0 and rank based on tweets reported select UserId,UserName,Nam...
Read more

How to update the user level when ever user report any tweet in sql

-
  ALTER PROCEDURE [dbo].[SP_UPDATEUSERLEVEL] @Userid INT AS BEGIN Declare @CountOfUserReportedTweets INT Declare @LevelBadgeId INT SET @CountOfUserReportedTweets=(select count(*) from OnGoingReports where UserId=@Userid) --CHECKS WHETHER THE LEVEL EXIST OR NOT IF EXISTS(select Top 1 * from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) BEGIN --IF LEVEL EXISTS UPDATE OR INSERT WITH THAT LEVEL ID SET @LevelBadgeId=(select Top 1 Id from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLevel SET LevelBadgeId=@LevelBadgeId WHERE UserId=@Userid END ELSE BEGIN INSERT INTO UserLevel(UserId,LevelBadgeId) VALUES (@Userid,@LevelBadgeId) END END ELSE BEGIN --IF NO LEVEL EXISTS THEN INSERTING DEFAULT LEVEL IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLeve...
Read more

Implement virtualisation in kendo drop down list using jquery ajax in mvc, aspx page

-
virtualization helps to load huge data fastly. $("#abcd").kendoDropDownList({                 filter: "contains",                 dataTextField: "WorkOrderNumber",                 dataValueField: "WorkOrderNumber",                 virtual: {                     itemHeight: 26,                     valueMapper: function (options) {                     }     ...
Read more