Hi Richa,
Refer below query.
In the below query first the records are read from the Json and records are inserted in Temp table without duplicate value.
Then using the While loop all the records are inserted in the Table.
SQL
DECLARE @Table1 AS TABLE
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[GNum] [varchar](16) NOT NULL,
[GNavn] [varchar](256) NOT NULL,
[GType] [varchar](256) NOT NULL,
[GStatus] [varchar](256) NULL,
[TypeNum] [varchar](256) NULL,
[ENum] [varchar](256) NULL,
[Area] [varchar](256) NULL
)
DECLARE @json VARCHAR(MAX)
SET @json = N'[{"Area":"160","GNum":"7752","ENum":"00150","GNavn":"Eid","GType":"Troms og Finnmark","GStatus":"I bruk","TypeNum":"258"},
{"Area":"1167","GNum":"496590.90","ENum":"00158","GNavn":"Eid","GType":"Vestland","GStatus":"I ABX bruk","TypeNum":"271"},
{"Area":"2438","GNum":"3876","ENum":"00061","GNavn":"Eid","GType":"Oslo","GStatus":"I Offentlig bruk","TypeNum":"80"},
{"Area":"1623","GNum":"2205","ENum":"00061","GNavn":"Eid","GType":"Oslo","GStatus":"I bruk","TypeNum":"86"},
{"Area":"348","GNum":673,"ENum":"00062","GNavn":"Eid","GType":"Oslo","GStatus":"I bruk","TypeNum":"99"},
{"Area":"374","GNum":"673","ENum":"00062","GNavn":"Eid","GType":"Oslo","GStatus":"I XYZ bruk","TypeNum":"100"},
{"Area":"1740","GNum":"1742","ENum":"00064","GNavn":"Eid","GType":"Vestland","GStatus":"I bruk","TypeNum":"100"},
{"Area":null,"GNum":"5254","ENum":"00067","GNavn":"Eid","GType":"Vestland","GStatus":"I bruk","TypeNum":"107"},
{"Area":null,"GNum":"4948","ENum":"00067","GNavn":"Festet","GType":"Vestland","GStatus":"I bruk","TypeNum":"108"},
{"Area":"3886","GNum":"7192","ENum":"00068","GNavn":"Eid","GType":"Agder","GStatus":"I 56 bruk","TypeNum":"109"},
{"Area":"1189","GNum":"1320","ENum":"00074","GNavn":"Eid","GType":"Oslo","GStatus":"I bruk","TypeNum":"141"},
{"Area":"1189","GNum":"1320","ENum":"14900","GNavn":"Eid","GType":"Oslo","GStatus":"I 123 bruk","TypeNum":"141"}]';
SELECT ROW_NUMBER() OVER (PARTITION BY Area ORDER BY Area ASC) AS rowNo,
[GNum], [GNavn], [GType], [GStatus], [TypeNum], [ENum], [Area]
INTO #Temp
FROM OPENJSON(@json, N'$')
WITH (
[GNum] NVARCHAR(256) N'$.GNum',
[GNavn] NVARCHAR(256) N'$.GNavn',
[GType] NVARCHAR(256) N'$.GType',
[GStatus] NVARCHAR(256) N'$.GStatus',
[TypeNum] NVARCHAR(256) N'$.TypeNum',
[ENum] NVARCHAR(256) N'$.ENum',
[Area] NVARCHAR(256) N'$.Area'
)
SELECT [GNum], [GNavn], [GType], [GStatus], [TypeNum], [ENum], [Area],
ROW_NUMBER() OVER (ORDER BY [GNum] DESC) AS RowNumber
INTO #Temp1
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY Area ORDER BY rowNo DESC) AS rowNu
FROM #Temp
) t
WHERE t.rowNu = 1
DECLARE @TotalCount INT = (SELECT COUNT(*) FROM #Temp1)
DECLARE @Counter INT = 1
WHILE (@Counter <= @TotalCount)
BEGIN
INSERT INTO @Table1 ([GNum], [GNavn], [GType], [GStatus], [TypeNum], [ENum], [Area])
SELECT [GNum], [GNavn], [GType], [GStatus], [TypeNum], [ENum], [Area]
FROM #Temp1
WHERE RowNumber = @Counter
SET @Counter = @Counter + 1
CONTINUE;
END
SELECT * FROM @Table1
DROP TABLE #Temp1
DROP TABLE #Temp
Note: There is no need for while loop if you don't wan to check the duplicate in database.
You can simply write insert query without where statement for all records to insert in the table.
If you want to check database for update then use IF EXISTS condition and update the record.
