This should work assuming that the JSON structure generated by
your initial query matches the structure you're
trying to parse with OPENJSON. Make sure that the column names and JSON keys
are correct. If your JSON structure is different, you might need to adjust the
paths accordingly.
.png)
CREATE TABLE #TempData (
Id INT,
Name NVARCHAR(MAX),
MobileNumber NVARCHAR(MAX)
)
-- Insert sample data into the temporary table
INSERT INTO #TempData (Id, Name, MobileNumber) VALUES
(1, 'A', '1234567890'),
(2, 'B', '1234567890'),
(3, 'C', '1234567890');
-- Select data from the temporary table
SELECT * FROM #TempData
-- Use FOR JSON PATH to construct a JSON array with data from the temporary table
SELECT (
SELECT [Name], [MobileNumber]
FROM #TempData
FOR JSON PATH
) AS Persons ---PASSS Persons name
-- Drop the temporary table
DROP TABLE #TempData
DECLARE @json NVARCHAR(MAX);
SELECT @json = (
SELECT Id, Name, MobileNumber
FROM tableb
FOR JSON PATH
);
SELECT @json AS JsonData;
-- Parse JSON using OPENJSON
SELECT *
FROM OPENJSON(@json) WITH (
Id INT '$.Id',
[Name] NVARCHAR(max) '$.Name',
[MobileNumber] NVARCHAR(max) '$.MobileNumber'
);
--
JSON_MODIFY example
SET @json = '{"Id": "1", "[Name]": 25,
"[MobileNumber]": 999 }';
SET @json = JSON_MODIFY(@json, '$.Age', 31);
SELECT @json AS ModifiedJson;
-- Define
the stored procedure
CREATE PROCEDURE InsertEmployee
@jsonData NVARCHAR(MAX)
AS
BEGIN
-- Your logic
to insert the JSON data into the database goes here
-- For example:
INSERT INTO
EmployeeTable (Name, Age)
SELECT Name, Age
FROM OPENJSON(@jsonData)
WITH (
Name NVARCHAR(50),
Age INT
)
END
--
Execute the stored procedure with the JSON data
DECLARE @json NVARCHAR(MAX)
SET @json = '{"Id": "1", "Name": "A",
"MobileNumber": 99999}'
EXEC InsertEmployee @jsonData = @json;
-- List
the contents of the EmployeeTable
SELECT * FROM EmployeeTable;
-- Stored
procedure for inserting data into tableB
CREATE PROCEDURE [dbo].[InsertIntoTableB]
@json NVARCHAR(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Create
temporary table to hold data from JSON
DROP TABLE IF EXISTS #tableB;
SELECT *
INTO #tableB
FROM OPENJSON (@json, '$.tableBModel')
WITH
(
Id UNIQUEIDENTIFIER '$.Id',
Name NVARCHAR(MAX) '$.Name',
IsDeleted BIT '$.IsDeleted'
);
-- Insert into
tableB
INSERT INTO tableB (Id, Name, IsDeleted, CreatedName, UpdatedName)
SELECT Id, Name, IsDeleted, 'Admin' AS CreatedName, 'Admin' AS UpdatedName
FROM #tableB;
-- Commit the
transaction if all operations succeed
COMMIT TRANSACTION;
SELECT 'Successful record Insert!' AS [Message], 1 AS Result;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT 'Please try again!' AS [Message], 0 AS Result;
END CATCH;
END
-- Stored
procedure for inserting data into tableC
CREATE PROCEDURE [dbo].[InsertIntoTableC]
@json NVARCHAR(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Create
temporary table to hold data from JSON
DROP TABLE IF EXISTS #tableC;
SELECT *
INTO #tableC
FROM OPENJSON (@json, '$.tableCModel')
WITH
(
Id UNIQUEIDENTIFIER '$.Id',
Description NVARCHAR(MAX) '$.Description',
IsDeleted BIT '$.IsDeleted'
);
-- Insert into
tableC
INSERT INTO tableC (Id, Description, IsDeleted, CreatedName, UpdatedName)
SELECT Id, Description, IsDeleted, 'Admin' AS CreatedName, 'Admin' AS UpdatedName
FROM #tableC;
-- Commit the transaction if all operations succeed
COMMIT TRANSACTION;
SELECT 'Successful record Insert!' AS [Message], 1 AS Result;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT 'Please try again!' AS [Message], 0 AS Result;
END CATCH;
END