SP that extract records from JSON and save/update the table record

daniel1001a 

Hi, Daniel.

I haven't played with JSON directly within SQL before, but here's a very, very basic working example.

There's no error checking or validation here and is based off your JSON data sample, which I've included again for completeness.

There's also no short-circuiting logic, meaning an UPDATE will always run for any row included within the JSON, even if none of the columns have changed value. This may or may not be of interest depending on the volume, but I would invest in doing this is if I were putting such a thing into production. However, it's beyond what I'm going to cover here as a guide.

Lastly, I've used a table variable as a convenience in this example. However, as noted in the official literature, this is not necessarily recommended if you anticipate processing more than 100 rows on a regular basis.

• table (Transact-SQL) - SQL Server | Microsoft Learn

I would strongly urge you to put more effort into a production implementation but this should at least get you heading in the right direction.

• OPENJSON (Transact-SQL) - SQL Server | Microsoft Learn

Sample tables

CREATE TABLE [dbo].[emp](
	[EmpId] [int] NOT NULL PRIMARY KEY,
	[EmpName] [varchar](64) NOT NULL,
	[TotalPoint] [float] NOT NULL,
);
GO

CREATE TABLE [dbo].[task](
	[TaskId] [int] NOT NULL PRIMARY KEY,
	[TaskName] [varchar](64) NOT NULL,
	[EmpId] [int] NOT NULL,
	[TaskSize] [float] NOT NULL,
);
GO

Stored procedure definition

CREATE PROCEDURE dbo.UpdateEmployeesAndTasks
(
	@json AS [nvarchar](max)
)
AS
BEGIN
	SET NOCOUNT ON;
	-- Topic #1: Employees.
	-- Declare the table variable definition for employees, noting table variables aren't recommended where it's expected that more than 100 rows would routinely exist.
	DECLARE @Employees table
	(
		employeeId [int] NOT NULL
		, employeeName [varchar](64) NOT NULL
		, totalPoint [float] NOT NULL
		, existingEmpId [int] NULL
	);

	-- Toss the relevant data into the table variable.
	INSERT INTO @Employees
		SELECT
			[json_e].employeeid AS [employeeId]
			, [json_e].employeename AS [employeeName]
			, [json_e].totalpoint AS [totalPoint]
			, e.EmpId AS [existingEmpId]
		FROM
			OPENJSON(@json)
			WITH
			(
				Employee nvarchar(max) AS JSON
			)
			OUTER APPLY OPENJSON(Employee)
			WITH
			(
				employeeid int
				, employeename varchar(64)
				, totalpoint float
			) AS [json_e]
			LEFT OUTER JOIN dbo.emp AS e ON [json_e].employeeid = e.EmpId;

	-- Perform relevant updates of existing employees.
	UPDATE [emp]
		SET
			[EmpName] = e.[employeeName]
			, [TotalPoint] = e.[totalPoint]
	FROM
		@Employees AS e
	WHERE
		e.existingEmpId IS NOT NULL
		AND [EmpId] = e.existingEmpId;

	-- Add any new employees.
	INSERT INTO dbo.emp (EmpId, EmpName, TotalPoint)
		SELECT
			e.employeeId
			, e.employeeName
			, e.totalPoint
		FROM
			@Employees AS e
		WHERE
			e.existingEmpId IS NULL;


	-- Topic #2: Tasks.
	-- Declare the table variable definition for tasks, noting table variables aren't recommended where it's expected that more than 100 rows would routinely exist.
	DECLARE @Tasks table
	(
		taskId [int] NOT NULL
		, taskName [varchar](64) NOT NULL
		, empId [int] NOT NULL
		, taskSize [float] NOT NULL
		, existingTaskId [int] NULL
	);

	-- Toss the relevant data into the table variable.
	INSERT INTO @Tasks
		SELECT
			[json_t].taskid AS [taskId]
			, [json_t].taskname AS [taskName]
			, [json_t].employeeid AS [empId]
			, [json_t].size AS [taskSize]
			, t.TaskId AS [existingTaskId]
		FROM
			OPENJSON(@json)
			WITH
			(
				Task nvarchar(max) AS JSON
			)
			OUTER APPLY OPENJSON(Task)
			WITH
			(
				taskid int
				, taskname varchar(64)
				, employeeid int
				, size float
			) AS [json_t]
			LEFT OUTER JOIN dbo.task AS t ON [json_t].taskid = t.TaskId;

	-- Perform relevant updates of existing tasks.
	UPDATE [task]
		SET
			[TaskName] = t.[taskName]
			, [EmpId] = t.[empId]
			, [TaskSize] = t.[taskSize]
	FROM
		@Tasks AS t
	WHERE
		t.existingTaskId IS NOT NULL
		AND [task].[TaskId] = t.existingTaskId;

	-- Add any new tasks.
	INSERT INTO dbo.task (TaskId, TaskName, EmpId, TaskSize)
		SELECT
			t.taskId
			, t.taskName
			, t.empId
			, t.taskSize
		FROM
			@Tasks AS t
		WHERE
			t.existingTaskId IS NULL;

	SET NOCOUNT OFF;
END;
GO

JSON sample data and EXEC example

DECLARE @json [nvarchar](max) = N'
{
	"Ticketid":"0001",
	"TicketName":"DAILYTICKET01011",
	"Employee": [
		{
			"employeeid":"1",
			"employeename":"David",
			"totalpoint":"8"
		},
		{
			"employeeid":"5",
			"employeename":"Mike",
			"totalpoint":"2"
		}
	],
	"Task": [
		{
			"taskid":"1",
			"taskname":"Task01",
			"employeeid": 2,
			"size":1
		},
		{
			"taskid":"4",
			"taskname":"Task50",
			"employeeid": 1,
			"size":5
		}
	]
}
';

EXEC dbo.UpdateEmployeesAndTasks @json;

You can change the values around in the JSON and re-run the query to see the impact on the two tables.

Cheers,

Lain