In my asp.net website i have a functionality where user can import data from excel. I am using SQLBulkCopy to implement it. I have a instead of Insert trigger ( to check for duplicates) on the table in which the data is being imported.
Following are 2 issues which i have.
Question 1. When excel contains only one record which is duplicated the trigger fires and work as expected. But if the excel has 2 or more dulplicated rows then the trigger does not work as expected and data is inserted.
Example Row in excel CustNo Name Address 1234 A A1
Row in table CustNo Name Address 1234 A Address
Record after sqlbulkcopy in table
CustNo Name Address 1234 A A1
But if there are 2 duplicate row in Excel
Row in excel CustNo Name Address 1234 A A1 1234 A1 A2
Row in table CustNo Name Address 1234 A Address
Record after sqlbulkcopy in table CustNo Name Address 1234 A A1 1234 A1 A2
the second duplicate row is inserted.
Question 2 Which is the best way to validate Data type before inserting the record through sqlbulkcopy and notify user at the end that there was invalid data in excel?
I know i can use the same instead of insert trigger to validate the data. but i would like to know how i can notify the user that excel has invalid rows.
Below is my trigger
ALTER TRIGGER [TRG_Prevent_Duplicate_Customer] ON [dbo].[Customer] INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON
BEGIN DECLARE @IsDuplicate int SELECT @IsDuplicate = count(*) FROM Customer a, inserted i WHERE a.CustNo = i.CustNo
if @IsDuplicate >0 begin update Customer set Name = i.Name , Address = i.Address from Customer a , inserted i where a.CustNo = i.CustNo
end
else begin
insert into [Customer]
SELECT a.CustNo,a.Address,a.Name,a.datecreated,a.createdby,a.datemodified,a.modifiedBy
FROM inserted a
where a.CustNo is not null and a.Address is not null and a.Name is not null
end
Thanks