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.

Advertisements

2 Responses to Prevent duplicate entry when using SqlBulkCopy

  1. Vidhya says:

    Could you plz provide me sample code on how to create index and call the index in code behind as i’m using bulkcopy to insert my xml files to sqlserver.It duplicates on each time I upload a new Xml data.I’m new to .net.Could you guide me in trying to solve this issue.

    • rakeshbajania says:

      Dear Vidhya,

      The index(es) is/are created from the sql server side. If you want to create it from the code behind then you can. You have to use the same syntax as we use for sql server and then create script and execute the same from the code behind.

      Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: