Get duplicate records before bulkcopy to database

Question

I am now copying data from one client database DB2 to main database DB1 by using SqlBulkCopy.

DataTable dtTable = new DataTable();
sqlDB2DataAdapter.Fill(dtTable); //select * from tblOrdersDB2

SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlDB1Connection)
bulkcopy.DestinationTableName = "dbo.tblOrdersDB1";
bulkcopy.WriteToServer(dtTable);

By creating INDEX ,when I will insert data with bulk to db, all duplicate values will not inserted.
The problem is I want to report a CSV file of all duplicate records being ignored.
How can I achieve this duplicate records?Is there a simple way to do that?
Thank you.

This may be a help: http://stackoverflow.com/questions/2593689/how-to-prevent-duplicate-records-being-inserted-with-sqlbulkcopy-when-there-is-n — Habib, Dec 14 '12 at 05:17
This is a way to ignored duplicated records when bulkcopy.My Problem is to get duplicated records to report CSV to user. — nnnn, Dec 14 '12 at 05:21

score 2 · Accepted Answer · answered Dec 14 '12 at 05:29

2

Why don't you just filter our the duplicate records during the initial SELECT? And as a separate task select and report the duplicates the user. The duplicate thing really is a separate concern from the bulk copy.

answered Dec 14 '12 at 05:29

Eren Ersönmez

38,383
7
71
92

score 1 · Answer 2 · answered Dec 14 '12 at 07:53

This may be a help

class TableComparer : EqualityComparer<DataRow>
{
    public override bool Equals(DataRow c1, DataRow c2)
    {
        if (c1["field1"] == c1["field1"] &&
            c1["field2"] == c1["field2"])
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public override int GetHashCode(DataRow c)
    {
        int hash = 23;
        hash = hash * 37 + c["field1"].GetHashCode();
        hash = hash * 37 + c["field2"].GetHashCode();
        return hash;
    }
}

    TableComparer eqc = new TableComparer();
    var newValues = tempList.Rows.Cast<DataRow>().Distinct(eqc).ToList();

SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlDB1Connection)
bulkcopy.DestinationTableName = "dbo.tblOrdersDB1";
bulkcopy.WriteToServer(newValues);

Thanks, what is TableComparer comparing? Duplicate Records in one DataTable? — nnnn, Dec 14 '12 at 08:10
TableComparer provides custom comparison. According to the constraints specified in all rowları checks. The records are then used to clean pair of — wolcy97, Dec 14 '12 at 09:29

Get duplicate records before bulkcopy to database

2 Answers2