Firstly, your delete statement will not work Error:
The multi-part identifier "xc.a" could not be bound.
Your alias should be either
xc
for the table or
xt.
for the columns. E.G.
delete #XMLTable from #XMLTable xc inner join main_table mt on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c]
Secondly, as you are using table aliases it is better to be consistent and use those aliases throughout e.g.
delete xc from #XMLTable xc inner join main_table mt on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c]
Probably the easiest way to add your validation message is to return an output parameter from your sproc e.g.
, @DupsFound int OUTPUT
and test for the duplicates
before deleting them e.g.
SELECT @DupsFound = COUNT(*)
FROM main_table mt
inner join #XMLTable xc on xc.[a] = mt.[a] AND xc.[b]=mt.[b] AND xc.[c]=mt.[c]
Your UI layer should display the message if @dupsfound is > 0
As an aside, rather than creating a temporary table everytime, why not have a permanent "staging" table
Lastly, you have created an
ID
identity field on the temporary table - if your main_table also has an identity ID column then you must use
INSERT INTO main_table
select [a],[b],[c] from #XMLTable
Don't use * as that will include the ID column which is populated automatically