1

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!

  • 2
    SELECT DISTINCT vnr FROM dzp.accounts_stage? – mustaccio Apr 10 '17 at 13:36
  • 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) – zuiqo Apr 10 '17 at 13:46
  • SELECT DISTINCT vnr FROM dzp.accounts_stage s WHERE NOT EXISTS (SELECT 1 FROM dzp.contractid WHERE vnr = s.vnr) – mustaccio Apr 10 '17 at 13:48
  • 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. – zuiqo Apr 10 '17 at 13:59
3

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.