title: How can I prevent inserting duplicate data into a SQL Server table where value is multiple
body: <p>I have a declared some of data that need to be inserted into SQL, what should I do to check the data in SQL to prevent same data inserted into table?</p>
  <p>123(AA) is already uploaded and 123(BB) is a new invitee.
  At this case, server should upload just 123(BB).</p>
  <pre><code> declare @inviteename VARCHAR(500) = 'aa,bb' ,@inviteephoneno VARCHAR(500) = '123,45',@Code varchar(10)='123', @CustomerID VARCHAR(50) = 10  
  
     DECLARE @tbl TABLE (
  inviteename VARCHAR(100), 
  inviteephoneno VARCHAR(100)
  );
  
  INSERT INTO @tbl ( inviteename, inviteephoneno) select  @inviteename, @inviteephoneno;
  
     DECLARE @temp TABLE (
  inviteename VARCHAR(100), 
  inviteephoneno VARCHAR(100),
  CustomerID VARCHAR(100)
  );
  
  ;WITH cte1 AS
  (
      SELECT  value AS inviteename , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
      FROM @tbl CROSS APPLY STRING_SPLIT(inviteename, ',')
  ), cte2 AS
  (
      SELECT  value AS inviteephoneno , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
      FROM @tbl CROSS APPLY STRING_SPLIT(inviteephoneno, ',')
  )
  
  INSERT INTO @temp ( inviteename, inviteephoneno,CustomerID)
  SELECT cte1.inviteename, cte2.inviteephoneno,@CustomerID   FROM cte1 INNER JOIN cte2 ON cte2.seq = cte1.seq; 
  
  if not  exists (select 1 from test a where a.inviteename= (select InviteeName from @temp a where a.CustomerID=@CustomerID)
   and a.InviteeMobileNumber = (select InviteeMobileNumber from @temp a where a.CustomerID=@CustomerID))    
    begin
    INSERT dbo.test (InviteeName ,InviteeMobileNumber ) 
     SELECT a.inviteename, a.inviteephoneno  FROM @temp a 
  
    end
  </code></pre>
  <p>test table</p>
  <pre><code>create table test ( 
  id int identity(1,1)
  , inviteename VARCHAR(100)
  , inviteephoneno VARCHAR(100) );
  </code></pre>