Inserting into temp table

Question

I am just getting data inserting to temp table.

DECLARE @XmlStringNPDBudget NVARCHAR(max) = '{"BudgetList":[{"BudgetId":4,"Month":1,"Year":2022,"Budget":750000},{"BudgetId":5,"Month":2,"Year":2022,"Budget":950000},{"BudgetId":0,"Month":3,"Year":0,"Budget":0},{"BudgetId":0,"Month":4,"Year":0,"Budget":0},{"BudgetId":0,"Month":5,"Year":0,"Budget":0},{"BudgetId":0,"Month":6,"Year":0,"Budget":0},{"BudgetId":0,"Month":7,"Year":0,"Budget":0},{"BudgetId":0,"Month":8,"Year":0,"Budget":0},{"BudgetId":0,"Month":9,"Year":"2022","Budget":"11111"},{"BudgetId":1,"Month":10,"Year":2022,"Budget":1000},{"BudgetId":2,"Month":11,"Year":2022,"Budget":350000},{"BudgetId":3,"Month":12,"Year":2022,"Budget":550000}]}'

DROP TABLE IF EXISTS #ParticularYearBudgetInsertUpdate

CREATE TABLE #ParticularYearBudgetInsertUpdate 
(
    [BudgetId] INT, 
    [Month] INT,
    [Year] INT,
    [Budget] DECIMAL
) 

INSERT INTO #ParticularYearBudgetInsertUpdate([BudgetId], [Month], [Year], [Budget])
    SELECT 
        [BudgetId], [Month], [Year], [Budget] 
    FROM 
        OPENJSON (@XmlStringNPDBudget)
        WITH (BudgetId INT, [Month] INT, [Year] INT, [Budget] DECIMAL)

SELECT * FROM #ParticularYearBudgetInsertUpdate

SELECT * FROM NPDBudget

It's displaying column name and one row of NULLs. please help me out

Answer 1

Your JSON data is contained in a BudgetList array - so you need to take that into account - try this code for the SELECT:

SELECT 
    [BudgetId], [Month], [Year], [Budget] 
FROM 
    OPENJSON (@XmlStringNPDBudget, N'$.BudgetList')
        WITH 
        (
            BudgetId INT '$.BudgetId', 
            [Month] INT '$.Month', [Year] INT '$.Year', 
            [Budget] DECIMAL '$.Budget'
        )