SQL Insert multiple values with UNIQUE constraint

Question

I have a MS SQL Server 2012 and the following table:

CREATE TABLE dzp.contractid (
id bigint PRIMARY KEY IDENTITY(1,1),
VNR char(18) NOT NULL,
CONSTRAINT UC_VNR UNIQUE (VNR))

For context, VNR represents a unique contract number in string form.

I want to import a csv file with contract information, with possibly multiple rows per contract. To avoid querying over a char(18), I am trying to normalize the data upon loading using the INSERT-Trigger of the staging table.

Flow is:

csv-file --> staging table --> insert trigger --> normalized tables

In my trigger, I am attempting the following:

BEGIN TRY
 INSERT INTO dzp.contractid(VNR)
 SELECT VNR
 FROM dzp.accounts_stage
END TRY
BEGIN CATCH
-- ignore unique constraint violation error, raise otherwise
IF ERROR_NUMBER() <> 2627
 RAISERROR ('blah', 16, 1)
END CATCH

So what I hope to achieve is basically:

1. Get all the contract numbers from the staging table
2. Add all these numbers to the normalized table
3. Ignore the error that gets thrown when the unique constraint is violated
4. When working correctly, I now have a table with all the unique VNRs

Problem:

The Uniqueness-Constraint seems to be blocking the entire INSERT, not only the values that are actually duplicates.

Is there some way of inserting data row-wise, or tell the constraint to treat it on a row-by-row-basis?

Is this even the right approach? I'm happy to completely change the approach, since I cannot imagine I'm the first to have this issue... For context, this is essentially a reporting database that gets batch-fed once per day, with about 1m rows in total (from the csv-table), and has ~5 users running queries from time to time.

Thanks for your input!

• mustaccio: SELECT DISTINCT vnr FROM dzp.accounts_stage?
• zuiqo: That doesnt work, because there are already VNRs in the table, and if that is the case, I want to insert those that are new, but not reject the entire block. The Select-Statement in the TRY-block yields about 250k rows, of which there are approximately 99% already known (count of the successfully inserted VNRs == new clients from that day)
• mustaccio: SELECT DISTINCT vnr FROM dzp.accounts_stage s WHERE NOT EXISTS (SELECT 1 FROM dzp.contractid WHERE vnr = s.vnr)
• zuiqo: Nice, that works! I could probably remove the entire TRY/CATCH as well, since I only run that query once per day. Post it as an answer and you'll get the point.

Answer 1

SQL constraint violation affects the entire statement, you cannot ignore individual rows.

What you can do is insert only the rows that don't violate the constraint. There are several ways to do that, for example

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT vnr FROM dzp.accounts_stage s 
WHERE NOT EXISTS (SELECT 1 FROM dzp.contractid WHERE vnr = s.vnr)

You could also use MERGE:

MERGE dzp.contractid AS t
USING dzp.accounts_stage AS s
ON (t.vnr = s.vnr) 
WHEN NOT MATCHED BY TARGET 
THEN INSERT(vnr) VALUES(s.vnr)

Answer 2

If multiple columns need to be unique, you can use "GROUP BY" clause with all unique fields.

Example:

INSERT INTO Table1Name (Field1, Field2, Field3)
 SELECT Field4, Field5, Field6
 FROM Table2Name
 GROUP BY Field4, Field5, Field6;