CODEWITHSUNDEEP
c#asp.netsql
John Kim
John Kim

Reputation: 43

How duplicate entries be prevented while using SqlBulkCopy while importing huge data?

I want to know how to prevent duplicate entry to database table in case the table already have a record for that field.

As in my table column name: Website is unique column. And my uploading excel file may have same record with new data or maybe its complete duplicate so based on Column name Website i want to prevent entry of that duplicate entry and then enter another next record and this goes on.

I hope its clear, here is my code:

    protected void btnSend_Click(object sender, EventArgs e)
{
    //file upload path
    string path = fileuploadExcel.PostedFile.FileName;
    //Create connection string to Excel work book
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\File.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False";
    //Create Connection to Excel work book
    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
    //Create OleDbCommand to fetch data from Excel
    OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
    excelConnection.Open();
    OleDbDataReader dReader;
    DataTable table = new DataTable();
    dReader = cmd.ExecuteReader();
    table.Load(dReader);
    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    //Give your Destination table name
    sqlBulk.DestinationTableName = "TableName";
    sqlBulk.WriteToServer(table);
    excelConnection.Close();

    int numberOfRowsInserted = table.Rows.Count;// <-- this is what was written.

    string message = string.Format("<script>alert({0});</script>", numberOfRowsInserted);
    ScriptManager.RegisterStartupScript(this, this.GetType(), "scr", message, false);
}

Upvotes: 0

Views: 1684

Answers (2)

Vadim
Vadim

Reputation: 2865

How about modifying the query you pass to OleDbCommand to select only the values of Website you need?

If the entire row is duplicate - you can use distinct. See How to select unique records by SQL for an example.

If only this column repeats and other columns are not relevant, then distinct may not work (it depends on the DB) and you will have to use GROUP BY and select the first row of each group.

Upvotes: 1

Sam
Sam

Reputation: 1366

One thing you can do is to load it into a temporary table first that has no restrictions. Then you can remove all records that do not match your business requirements (such as duplicate keys) and log what records you removed and why (optional, but can be useful). Finally, you can insert/merge the temp table into the final table.

Alternatively, you can load everything into your temporary table and put the business logic in the insert/merge statement, only inserting the valid records that way.

Upvotes: 0

Related Questions