Inserting collection of string to DB with duplicate check

Hi all,

We need some kind of functionality where from frontend [ASP.NET] we pass the comma or semicolon saperated strings into database for inserting purpose and also we need to check the duplication of the string.

With the world of XML we can do this easily, here is the Sql Script to do so. My string collection is the email list, i have to insert multiple emails and also need to check for the duplication. at the end I get the duplicate email list, which can be easily get form Frondend [ADO.NET] using output param or any other way.

SET NOCOUNT ON

DECLARE @Table AS TABLE
(
    Email VARCHAR(100)
)
 
INSERT INTO @Table VALUES('a@b.com')
INSERT INTO @Table VALUES('c@d.com')
  
DECLARE @EmailList VARCHAR(MAX)
DECLARE @XMLEmailList XML
  
SELECT @EmailList = 'a@b.com;c@d.com;aa@aa.com;fads@ddd.com'
SELECT @XMLEmailList= '<email>' +  REPLACE(@EmailList, ';', '</email><email>') + '</email>'
  
DECLARE @DuplicateEmail VARCHAR(100)
  
SELECT @DuplicateEmail = COALESCE(CAST(@DuplicateEmail AS VARCHAR(MAX)) + ';', '') + + CAST(a.value('.', 'varchar(100)') AS VARCHAR(10))
FROM @XMLEmailList.nodes('/email') v(a)
WHERE a.value('.', 'varchar(100)') in (SELECT Email FROM @Table)
  
INSERT INTO @table
SELECT a.value('.', 'varchar(100)')
FROM @XMLEmailList.nodes('/email') v(a)
WHERE a.value('.', 'varchar(100)') not in (SELECT Email FROM @Table)
  
SELECT @DuplicateEmail
SELECT * FROM @Table