I want to update/insert records from Json table ( which has 5000 records) to another table . when I run for first time Insert works fine and when I update ,it updates accordingly. But when I ran again the same file with additional records it doesn't insert the records. can someone help me with this ? Below is my code
ALTER PROCEDURE [dbo].[load_consultation_data]
@consultation_json NVARCHAR(MAX)
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
DECLARE @JSON VARCHAR(MAX)
SET @JSON = @consultation_json
DECLARE @jsontable table
(
customerID NVARCHAR(50),
clinicID NVARCHAR(50),
birdId NVARCHAR(80),
consultationId NVARCHAR(80),
employeeName NVARCHAR(150),
totalPrice MONEY,
row_num int
)
/* Importing nested Json data */
INSERT INTO @jsontable
SELECT customer.customerID AS customerID
,customer.clinicID AS clinicID
,consultation.birdId AS birdId
,consultation.consultationId AS consultationId
,consultation.employeeName AS employeeName
,consultation.totalPrice AS totalPrice
,ROW_NUMBER() OVER (ORDER BY consultationId ) row_num
FROM OPENJSON (@JSON, '$')
WITH (customerID VARCHAR(50) '$.customerID',
clinicID VARCHAR(50) '$.clinic.clinicID') as customer
CROSS APPLY openjson(@json,'$.clinic.consultation')
WITH(
birdId NVARCHAR(80),
consultationId NVARCHAR(80),
employeeName NVARCHAR(150),
totalPrice MONEY
) as consultation
INNER JOIN dbo.clinic AS clinic_tab ON clinic_tab.external_id = customer.clinicID
INNER JOIN dbo.bird AS bird_tab ON bird_tab.external_bird_id = consultation.birdId
/** If consultation record doesn't exists then do insert else update **/
IF NOT EXISTS
(
SELECT 1
FROM dbo.consultation AS con
INNER JOIN dbo.bird AS al ON con.external_bird_id = al.external_bird_id AND al.clinic_id = con.clinic_id
INNER JOIN dbo.clinic AS pr ON con.clinic_id = pr.id
INNER JOIN @jsontable AS rjson ON rjson.consultationId = con.external_consultation_id
WHERE con.external_consultation_id = rjson.consultationId
AND pr.external_id = rjson.clinicID
AND al.external_bird_id = rjson.birdId )
BEGIN
/** Insert recored into consultation table **/
INSERT INTO dbo.[consultation]
(
[external_consultation_id]
,[external_bird_id]
,[clinic_id]
,[bird_id]
,[employee_name]
,[total_price]
)
SELECT rjson.consultationId AS [external_consultation_id]
,rjson.[birdId] AS [external_bird_id]
,bird_tab.clinic_id AS [clinic_id]
,bird_tab.[id] AS [bird_id]
,rjson.employeeName AS [employee_name]
,rjson.totalPrice AS [total_price]
FROM @jsontable as rjson
INNER JOIN dbo.bird AS bird_tab on bird_tab.external_bird_id = rjson.birdId
INNER JOIN dbo.clinic AS clinic_tab on clinic_tab.external_id = rjson.clinicID
WHERE rjson.consultationId = rjson.consultationId --@consultationID
and bird_tab.clinic_id = clinic_tab.id
END
ELSE
BEGIN
/* Update Records into consultation table */
UPDATE dbo.[consultation] SET
[external_bird_id] = rjson.[birdId]
,[clinic_id] = al.clinic_id
,[bird_id] = al.[id]
,[employee_name] = rjson.employeeName
,[total_price] = rjson.totalPrice
FROM dbo.consultation AS con
INNER JOIN dbo.bird AS al ON con.external_bird_id = al.external_bird_id AND al.clinic_id = con.clinic_id
INNER JOIN dbo.clinic AS pr ON con.clinic_id = pr.id
INNER JOIN @jsontable AS rjson ON rjson.consultationId = con.external_consultation_id
WHERE con.external_consultation_id = rjson.consultationId
AND pr.external_id = rjson.clinicID
AND al.external_bird_id = rjson.birdId
END
END
