I am facing issue with inserting the JSON array into tables
Doc Table : Id, Name, Desc, RefName, IsActive
Con Table : Id, Field, Criter, DId, Conjunction, IsActive
We map doc table Id Column to DId column
This is sample JSON
{
"SubDoc" : [{ "Id": 0, "Name": "file1", "Desc": "stockvalue", "RefName": "", "IsActive": "true", "Conditions": [{ "Id": 0, "Field": 1, "Criter": 0.00, "DId": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "DIdId": 0, "Conjuction": "AND", "IsActive": "true"}]}, { "Id": 0, "Name": "file1", "Desc": "", "RefName": "", "IsActive": "true", "Conditions": [{ "Id": 0, "Field": 1, "Criter": 0.00, "DIdId": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "DIdId": 0, "Conjuction": "AND", "IsActive": "true"}]
}]}
ALTER PROCEDURE [dbo].[SaveDoc]
@DocRequest NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Conditions nvarchar(max)
DECLARE @SubDoc nvarchar(max)
DECLARE @Id int
DECLARE @index INT = 0
DECLARE @count INT
SELECT @count = COUNT(*) FROM OPENJSON(@DocRequest,'$.SubDoc');
PRINT 'count'+ CAST(@count AS NVARCHAR(MAX))
BEGIN TRANSACTION
BEGIN TRY
BEGIN
PRINT '0'
WHILE @index < @count
BEGIN
SELECT * INTO #SubJsonRequest
FROM OPENJSON (@DocRequest, '$.SubDoc[' + CAST(@index AS NVARCHAR(MAX)) + ']')
WITH (
[Id] int '$.Id',
[Name] [nvarchar](150) '$.Name',
[Desc] [nvarchar](max) '$.Desc',
[RefName] [nvarchar](200) '$.RefName',
[SourceLink] [nvarchar](200) '$.SourceLink',
[IsActive] [bit] '$.IsActive',
Conditions NVARCHAR(MAX) '$.Conditions'AS JSON
)
Select @Conditions = [Conditions] from #SubJsonRequest
INSERT INTO [dbo].[Document]([Name],[Desc],[RefName] ,[SourceLink],[IsActive])
(SELECT [Name],[Desc],[RefName] ,[SourceLink],[IsActive] FROM #DocRequest)
SELECT SCOPE_IDENTITY() AS Id
END
set @Id = SCOPE_IDENTITY()
PRINT @Conditions
PRINT @DocumentId
EXEC [dbo].SaveCondition @Conditions,@Id
DROP TABLE #SubJsonRequest
Set @index = @index + 1
PRINT @index
END
PRINT '1'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
DECLARE @Message varchar(MAX) = CONCAT('ERROR Line: ', CAST(ERROR_LINE() AS nvarchar(10)), 'Message: '+ ERROR_MESSAGE())
RAISERROR(@Message,16,1)
ROLLBACK;
END CATCH
END
When I am run the Stored Procedure it shows below Error Message:
Invalid object name '#SubJsonRequest'.
As per my knowledge while loop is not working properly. Can someone help me how to loop the array value and insert to tables
ALTER PROCEDURE [dbo].[SaveDoc]
@DocRequest NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Conditions nvarchar(max)
DECLARE @SubDoc nvarchar(max)
DECLARE @Id int
DECLARE @index INT = 0
DECLARE @count INT
SELECT @count = COUNT(*) FROM OPENJSON(@DocRequest,'$.SubDoc');
PRINT 'count'+ CAST(@count AS NVARCHAR(MAX))
BEGIN TRANSACTION
BEGIN TRY
BEGIN
WHILE @index < @count
BEGIN
IF OBJECT_ID('tempdb..#SubJsonRequest') IS NOT NULL
BEGIN
DROP TABLE #SubJsonRequest
END
SELECT * INTO #SubJsonRequest
FROM OPENJSON (@DocRequest, '$.SubDoc[' + CAST(@index AS NVARCHAR(MAX)) + ']')
WITH (
[Id] int '$.Id',
[Name] [nvarchar](150) '$.Name',
[Desc] [nvarchar](max) '$.Desc',
[RefName] [nvarchar](200) '$.RefName',
[SourceLink] [nvarchar](200) '$.SourceLink',
[IsActive] [bit] '$.IsActive',
Conditions NVARCHAR(MAX) '$.Conditions'AS JSON
)
Select @Conditions = [Conditions] from #SubJsonRequest
Set @index = @index + 1
INSERT INTO [dbo].[Document]([Name],[Desc],[RefName] ,[SourceLink],[IsActive])
(SELECT [Name],[Desc],[RefName] ,[SourceLink],[IsActive] FROM #DocRequest)
SELECT SCOPE_IDENTITY() AS Id
END
set @Id = SCOPE_IDENTITY()
EXEC [dbo].SaveCondition @Conditions,@Id
DROP TABLE #SubJsonRequest
PRINT @index
END
PRINT '1'
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
DECLARE @Message varchar(MAX) = CONCAT('ERROR Line: ', CAST(ERROR_LINE() AS nvarchar(10)), 'Message: '+ ERROR_MESSAGE())
RAISERROR(@Message,16,1)
ROLLBACK;
END CATCH
END