Prevent duplicate entry when using SqlBulkCopy

As we know, the fastest way to insert data in Database is to insert bulk data. SqlBulkCopy Class in System.Data.SqlClient Namespace provides simplest and fastest way to copy large amounts of data to SqlServer. Instead of inserting all data row by row, SqlBulkCopy process all data at once, thus making the insertions fast.

An example should be given below:

public void CopyData(DataTable TableToCopy, string SqlTableName)

{

        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["conStr"].ToString());

if (con.State == ConnectionState.Open)

{

con.Close();

}

con.Open();

SqlBulkCopy bc = new SqlBulkCopy(con);

bc.ColumnMappings.Add(0, 0);

bc.ColumnMappings.Add(1, 1);

bc.ColumnMappings.Add(2, 2);

bc.ColumnMappings.Add(3, 3);

bc.ColumnMappings.Add(4, 4);

bc.ColumnMappings.Add(5, 5);

if (SqlTableName != “DBTable”)

{

bc.ColumnMappings.Add(6, 6);

bc.ColumnMappings.Add(7, 7);

bc.ColumnMappings.Add(8, 8);

}

bc.DestinationTableName = SqlTableName;

bc.WriteToServer(TableToCopy);

con.Close();

}

Here, WriteToServer() method, do bulk insert in database. So SqlBulkCopy is very useful in the case that when we want to insert number rows in the Database at the same time not one by one. But in some situation we are in need to check for Duplicate records while doing multiple insertion. But SqlBulkCopy does not provide any built-in method or event to do that.

One possible solution is to make the column(s) of the table unique. And for that we have to create a Index on the Table with column names that we want to check for Uniqueness.

Following is the example for the same:

 

CREATE INDEX myIndex on [Table Name]

(

[Column name1],  [Column name2], ..

)

WITH (IGNORE_DUP_KEY = ON)

 

This index would create a unique index for the columns specified and it checks automatically that if any duplicate rows are when we do BulkCopy. If yes then do nothing else inserts bulk data into the Database.