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

Question

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);
}

may be you should use IEnumerable to check uniqueness and later you insert them into database — aizaz, Aug 07 '13 at 07:56

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

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.

edited May 23 '17 at 11:58

Community

1
1

answered Aug 07 '13 at 07:53

Vadim

2,847
15
18

So if i use that it will remove duplicate record from my excel file being uploaded matching existing records in the Table ? – John Kim Aug 07 '13 at 09:04
I updated my answer, you will have to construct an SQL query that will return what you want. After that, your code will already take care of moving only these rows into wherever you need – Vadim Aug 07 '13 at 09:45
can i use this in my case: CREATE UNIQUE INDEX MyIndex ON ExcelTable(id, data) WITH IGNORE_DUP_KEY – John Kim Aug 07 '13 at 09:48
i guess azure don't allow Unique key creation.. :( – John Kim Aug 07 '13 at 10:23
i have tried this: GO CREATE UNIQUE INDEX CID ON contact(CID, Website) WITH IGNORE_DUP_KEY and now i am getting this error: External table is not in the expected format. – John Kim Aug 07 '13 at 10:38

score 0 · Answer 2 · answered Aug 07 '13 at 07:55

0

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.

answered Aug 07 '13 at 07:55

Sam

1,358
15
24

@JohnKim You'll need some way to filter out the 'bad' records, so if you only do the most basic things needed for this solution (temp table + insert/merge query), the only extra work (compared to other solutions, like the one Vadim gave) would be the creation of a temp table. Granted, the creation of new tables can be bothersome depending on how easy it is to roll out database changes :) – Sam Aug 07 '13 at 08:09

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

2 Answers2