I have a database where tables are like:
tblCustomer(UserID [Primary Key],Facebook,Twitter,PhoneNum); tblSales(InvoiceID [Primary Key],CustomerID [Foreign Key],ProductID [Foreign Key]);
I'm importing some paper-based records and they're in chronological (time) order, with following columns:
For some reason we don't have existing ID systems for customers so UserID will be auto-generated at import-time. In my scenario, any of Facebook, Twitter or Phone number can uniquely identify a customer so I have unique index for each of them to enforce uniqueness constraint.
I created a view to facilitate data import:
A common case is that customer's Facebook (or other contact method) appears in multiple sales records. A trigger is created to handle such cases:
CREATE TRIGGER ON dbo.viewDataEntry INSTEAD OF INSERT AS BEGIN TRY INSERT INTO dbo.tblCustomer(Facebook,Twitter,PhoneNum) SELECT Facebook,Twitter,PhoneNum FROM inserted; END TRY BEGIN CATCH IF ERROR_NUMBER() != 2601 --To ignore uniqueness violation exception THROW; END CATCH DECLARE @UserID INT; SET @UserID = (SELECT UserID FROM dbo.tblCustomer AS O,inserted AS I WHERE (O.PhoneNum = I.PhoneNum OR O.Facebook = I.Facebook OR O.Twitter = I.Twitter)); INSERT INTO dbo.tblSales(CustomerID,ProductID) SELECT @UserID,ProductID FROM inserted; GO
Intended outcome is:
If duplicate Customer records are imported, drop duplicate, insert into sales table only;
If new Customer record is imported, create record for both customer and sales table.
However whenever duplicate values are entered I run into Error 3910 or 3616, which means the transaction is un-committable. I think it's because insertion into customer table need to be rolled back and I know that I cannot rollback part of the transaction while keeping the remaining part (which is, unfortunately, the intended outcome).
I found MERGE statement but it has too many restrictions (like WHEN MATCHED must be followed by UPDATE and DELETE).
Please kindly provide any working solution.