views:

11

answers:

0

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