Delete in Main SQL table if exists if a value has multiple status then insert it to New SQL table

You can use GROUP BY plus HAVING to get a list of the duplicated records, something like:

INSERT INTO NewInsertTable
        ( UniqueID ,
          Data ,
          Filename ,
          CheckNumber ,
          PaymentMethodCode ,
          TotalCheckAmount ,
          CheckDate ,
          EmployeeName ,
          IDNumber ,
          ClaimNo ,
          ClaimInfoNo ,
          CaseNo ,
          Status ,
          SubmittedAmount ,
          PaidAmount
        )
SELECT UniqueID ,
       Data ,
       Filename ,
       CheckNumber ,
       PaymentMethodCode ,
       TotalCheckAmount ,
       CheckDate ,
       EmployeeName ,
       IDNumber ,
       ClaimNo ,
       ClaimInfoNo ,
       CaseNo ,
       Status ,
       SubmittedAmount ,
       PaidAmount
FROM dbo.MainTable
WHERE IDNumber IN(
	SELECT IDNumber
	FROM @YourTable
	GROUP BY IDNumber
	HAVING COUNT(IDNumber) > 1
	)

Select allOpen in new window

Deleting them out of the main table is a little trickier because you need a way for the database to decide which one should be deleted. Let's assume the CheckDate is actually a DateTime and you want to KEEP only the most recent entry. You can use the ROW_NUMBER function, partitioned by the IDNumber and ordered by that date field to get a line item number per user:

; WITH Duplicates
AS (
	SELECT UniqueID
		, IDNumber
		, CheckDate
		, IDPerUser = ROW_NUMBER() OVER(PARTITION BY IDNumber ORDER BY CheckDate DESC)
	FROM dbo.MainTable
	)
DELETE m
FROM dbo.MainTable m
	INNER JOIN Duplicates d
		ON d.UniqueID = m.UniqueID
WHERE d.IDPerUser <> 1;

Select allOpen in new window

@RussellFox:

I apologize I just got back to you today.

I will test it and let you know.

Thank you.

@RussellFox:

The query did not output what I needed. If employee name has multiple "status" then insert it to a another table.

Thank you.

Any help please? Thank you.

This query not quite what I need:

SELECT  t1.UniqueID
             , t1.Data
	     , t1.Filename
	     , t1.CheckNumber
	     , t1.CheckDate
	     , t1.Payment
	     , t1.EmployeeName
	     , t1.CaseNo
	     , t1.[Status] 

FROM dbo.MainTable t1

WHERE EXISTS
(
   SELECT 1 FROM dbo.MainTable t2
   WHERE t2.EmployeeName = t1.EmployeeName
   AND  t2.CaseNo = t1.CaseNo
   AND t2.[Status] = t1.[Status] 
)


order by t1.EmployeeName ASC, t1.CaseNo ASC

Select allOpen in new window

Thank you again for any help.

Thank you Russell.