MSSQL Unique identifier based on row content

You can write a .NET Assembly to create the hash and then use it in a CLR UDF. So you can use that function to create the unique identifier while inserting data.

Hi

Do you have an example of how to do that?

Thanks

Brasso

You can use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) to Delete Duplicate Rows in SQL Table

e.g.,
http://www.kodyaz.com/articles/delete-duplicate-rows-using-row-number-partition-by-order-by.aspx

Hi

Necessarily want to delete them, I just want to be made aware of them.

Thanks

Brasso

so instead of deleting you can insert them into another table of same structure.

eg.,

WITH [CTE DUPLICATE] AS
(
SELECT
  RN = ROW_NUMBER() OVER (PARTITION BY CompanyTitle ORDER BY LastContactDate DESC),
  Id, CompanyTitle, ContactName, LastContactDate
FROM Suppliers
)
Insert into Suppliers_Duplicates select *  FROM [CTE DUPLICATE] WHERE RN > 1

Select allOpen in new window

here Suppliers and Suppliers_Duplicates should have same structure.

Better:-

WITH [CTE DUPLICATE] AS
(
SELECT
  RN = ROW_NUMBER() OVER (PARTITION BY CompanyTitle ORDER BY LastContactDate DESC),
  Id, CompanyTitle, ContactName, LastContactDate
FROM Suppliers
)
Insert into Suppliers_Duplicates ( Id, CompanyTitle, ContactName, LastContactDate )
select  Id, CompanyTitle, ContactName, LastContactDate  FROM [CTE DUPLICATE] WHERE RN > 1

The correct way to do this is always, always import into a staging table, validate your data prior to inserting into the final production table(s).  Remember the old axiom: Garbage in, garbage out.

>>I want to ensure that it is not duplicate data. <<
Before we can help you here, we need to know your definition of "duplicate data", is it:
1. Duplicate imported data or
2. Duplicate data when comparing imported data to existing data or
3. Something else.

Hi Acperkins

This is exactly what I want to do.   I want to validate on both point one and two.  

Thanks

Brasso