-- Assuming, for simplicity sake, that your main table is defined like:



CREATE TABLE MainTable (

    PKField INT PRIMARY KEY,

    OtherFields SQL_VARIANT /* or whatever.... */ NOT NULL

    )



-- I would create a staging table with an additional identity field, thus:



CREATE TABLE StagingData (

    IdentityField INT IDENTITY,

    PKField INT NOT NULL,

    OtherFields SQL_VARIANT /* or whatever.... */ NOT NULL

    )



-- Then a view that eliminates duplicates within the staging table and

-- also excludes staging data that duplicates rows already in the 

-- main table



CREATE VIEW LoadableIDs

AS

SELECT LoadableID = MIN(IdentityField) 

FROM StagingData 

WHERE NOT EXISTS (SELECT PKField From MainTable WHERE MainTable.PKField = StagingData.PKField)

GROUP BY PKField



-- Then...

-- 

-- Load all text data to the staging table

--

-- copy all rows from [StagingData] to [ViolationTable] 

--     WHERE IdentityField NOT IN (SELECT LoadableID FROM LoadableIDs)

-- 

-- copy all rows from [StagingData] to [MainTable] 

--     WHERE IdentityField IN (SELECT LoadableID FROM LoadableIDs)