如何避免护理院系统JSON重复条目插入SQL Server Residents表？

Hi everyone, I'm facing a duplicate record problem when importing JSON data from our care home management system, and I'm looking for help with one of two possible fixes. Let me break down the issue:

Background #

Here's a sample of the JSON data I'm working with (two duplicate resident records):

[{"Location":"Home 1","Community":"Ground Floor","FirstName":"Resident 1","PreferredName":"Res","LastName":"ResName","Gender":"Female","DateOfBirth":"1929-01-19T00:00:00","NINumber":null,"NHSNumber":"1234567890","FileOpened":"2019-10-18T00:00:00","Room":"B2","ExternalReference":"","RisksToBeAwareOf":"I am very hard of hearing and have vascular dementia which can cause me to have short term memory loss, however i am normally lucid and able to make my own choices and decisions.","PersonID":"5654689568973897589345","ConnectionID":"834895934t89f87438957","LocationID":"7187f0ac-b8f2-4f06-87ba-472aff3afa5e","CommunityID":"bdaf4054-8efa-4dac-ae40-52272a9001d1"},{"Location":"Home 1","Community":"Residents","FirstName":"Resident 1","PreferredName":"Res","LastName":"ResName","Gender":"Female","DateOfBirth":"1929-01-19T00:00:00","NINumber":null,"NHSNumber":"1234567890","FileOpened":"2019-10-18T00:00:00","Room":"B2","ExternalReference":"","RisksToBeAwareOf":"I am very hard of hearing and have vascular dementia which can cause me to have short term memory loss, however i am normally lucid and able to make my own choices and decisions.","PersonID":"5654689568973897589345","ConnectionID":"834895934t89f87438957","LocationID":"7187f0ac-b8f2-4f06-87ba-472aff3afa5e","CommunityID":"bdaf4054-8efa-4dac-ae40-52272a9001d1"}]

These two records are identical except for the Community field (which represents a floor/unit). The duplicates happen because the system assigns the same resident to multiple groups, and the JSON export doesn't let us control this behavior.

What I've Tried #

I tried adding a unique constraint in my import stored procedure to block duplicates, but it didn't work at all—neither duplicates with different Community values nor duplicates in the same Community are being blocked. Oddly enough, this same constraint logic works fine in other tables and stored procedures. Here's the code I used:

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
        ON cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
    WHERE tc.CONSTRAINT_TYPE = 'UNIQUE' 
      AND tc.TABLE_NAME = 'Residents' 
      AND cu.COLUMN_NAME LIKE '%LastName%') = 0 
BEGIN 
    ALTER TABLE [Residents] ADD CONSTRAINT U_NAME_Residents 
    UNIQUE(FirstName, LastName, DateOfBirth, ConnectionID, PersonID); 
END

What I'm Looking For #

I don't want to just run a DELETE FROM [Residents] WHERE Community = 'TargetValue' after importing. Instead, I need one of these two solutions:

1. A trigger that prevents any record with matching FirstName, LastName, DateOfBirth, ConnectionID, and PersonID from being inserted—regardless of the Community value.
2. A way to filter the JSON data before importing, either by excluding specific Community values or deduplicating entries based on the five unique fields mentioned above.

Any help with either approach would be greatly appreciated!

Solution 1: INSTEAD OF INSERT Trigger to Block Duplicates #

This trigger will check if a record with the same key fields already exists in the Residents table before allowing an insert. Only new, non-duplicate records will be added:

CREATE TRIGGER trg_PreventDuplicateResidents
ON [Residents]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert only records that don't already match the key fields
    INSERT INTO [Residents]
    (Location, Community, FirstName, PreferredName, LastName, Gender, DateOfBirth,
     NINumber, NHSNumber, FileOpened, Room, ExternalReference, RisksToBeAwareOf,
     PersonID, ConnectionID, LocationID, CommunityID)
    SELECT i.*
    FROM inserted i
    LEFT JOIN [Residents] r
        ON r.FirstName = i.FirstName
        AND r.LastName = i.LastName
        AND r.DateOfBirth = i.DateOfBirth
        AND r.ConnectionID = i.ConnectionID
        AND r.PersonID = i.PersonID
    WHERE r.PersonID IS NULL; -- No matching record exists
END;
GO

Note: If you want to keep one of the duplicate Community entries (e.g., prioritize "Ground Floor" over "Residents"), you can adjust the logic to check for existing records and skip duplicates, or even update the existing record's Community if needed.

Solution 2: Filter/Deduplicate JSON Before Import #

If you prefer to clean the JSON data before inserting it into the table, you can use OPENJSON with deduplication logic. Here are two options:

Option A: Exclude Specific Community Values

If you know which Community values you want to skip, filter them out during the import:

DECLARE @JsonData NVARCHAR(MAX) = N'[Your JSON string here]';

INSERT INTO [Residents]
(Location, Community, FirstName, PreferredName, LastName, Gender, DateOfBirth,
 NINumber, NHSNumber, FileOpened, Room, ExternalReference, RisksToBeAwareOf,
 PersonID, ConnectionID, LocationID, CommunityID)
SELECT Location, Community, FirstName, PreferredName, LastName, Gender, DateOfBirth,
       NINumber, NHSNumber, FileOpened, Room, ExternalReference, RisksToBeAwareOf,
       PersonID, ConnectionID, LocationID, CommunityID
FROM OPENJSON(@JsonData)
WITH (
    Location NVARCHAR(100),
    Community NVARCHAR(100),
    FirstName NVARCHAR(100),
    PreferredName NVARCHAR(100),
    LastName NVARCHAR(100),
    Gender NVARCHAR(10),
    DateOfBirth DATETIME,
    NINumber NVARCHAR(20),
    NHSNumber NVARCHAR(20),
    FileOpened DATETIME,
    Room NVARCHAR(20),
    ExternalReference NVARCHAR(MAX),
    RisksToBeAwareOf NVARCHAR(MAX),
    PersonID NVARCHAR(MAX),
    ConnectionID NVARCHAR(MAX),
    LocationID UNIQUEIDENTIFIER,
    CommunityID UNIQUEIDENTIFIER
)
WHERE Community NOT IN ('Residents'); -- Exclude the unwanted Community value

Option B: Deduplicate JSON Entries

If you want to keep only one entry per resident (regardless of Community), use ROW_NUMBER() to pick the first occurrence:

DECLARE @JsonData NVARCHAR(MAX) = N'[Your JSON string here]';

WITH JsonDeduplicated AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY FirstName, LastName, DateOfBirth, ConnectionID, PersonID
               ORDER BY Community -- Optional: prioritize a specific Community order
           ) AS RowNum
    FROM OPENJSON(@JsonData)
    WITH (
        Location NVARCHAR(100),
        Community NVARCHAR(100),
        FirstName NVARCHAR(100),
        PreferredName NVARCHAR(100),
        LastName NVARCHAR(100),
        Gender NVARCHAR(10),
        DateOfBirth DATETIME,
        NINumber NVARCHAR(20),
        NHSNumber NVARCHAR(20),
        FileOpened DATETIME,
        Room NVARCHAR(20),
        ExternalReference NVARCHAR(MAX),
        RisksToBeAwareOf NVARCHAR(MAX),
        PersonID NVARCHAR(MAX),
        ConnectionID NVARCHAR(MAX),
        LocationID UNIQUEIDENTIFIER,
        CommunityID UNIQUEIDENTIFIER
    )
)
INSERT INTO [Residents]
(Location, Community, FirstName, PreferredName, LastName, Gender, DateOfBirth,
 NINumber, NHSNumber, FileOpened, Room, ExternalReference, RisksToBeAwareOf,
 PersonID, ConnectionID, LocationID, CommunityID)
SELECT Location, Community, FirstName, PreferredName, LastName, Gender, DateOfBirth,
       NINumber, NHSNumber, FileOpened, Room, ExternalReference, RisksToBeAwareOf,
       PersonID, ConnectionID, LocationID, CommunityID
FROM JsonDeduplicated
WHERE RowNum = 1; -- Keep only the first entry per resident

内容的提问来源于stack exchange，提问作者Ant