In SqlBulkCopy, how to avoid duplicate insertion of records in the table?


I am using SqlBulkCopy to insert millions of data from table1 to table2. I am having two questions:I am using sql serevr 2008 and c#

1. while inserting to table2, how to avoid duplicate insertion of records?
2. If there is any issue on 100 record, the how to continue insertion for the remaining records? Record that is failed will save in the log file with the error message

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saroj13Author Commented:

Here is my code for BulkInsert. I need to create a composite key (IP, datetime,querytime). this should be unique for each insertion.  Please make change in the code
1: for avoiding duplicate nows to be inserting
2. in case of failure to insert some record, continue the process of insertion for other records. failed records will be saved in the log file.

   SqlConnection conn = new SqlConnection(sDBConn);

                DataTable table = new DataTable("ParsedDataTable");
                DataColumn column;
                DataRow row;
                column = new DataColumn();
                int blockSize = 1000;

                table.Columns.Add("IP", typeof(String));
                table.Columns.Add("DateTime", typeof(DateTime));
                table.Columns.Add("QueryTerm", typeof(String));
                 table.Columns.Add("UserRole", typeof(String));
                table.Columns.Add("Results", typeof(int));
  foreach (Record item in recordList)
                    row = table.NewRow();

                     row["IP"] = item.IP;
                    row["DateTime"] = item.DateTime;
                    row["QueryTerm"] = item.sQueryTerm;
                    row["UserRole"] = item.UserRole;
                    row["Results"] = item.Results;

                    if (table.Rows.Count >= blockSize)
                        IsSuccess = BulkInsert(table, conn);


                IsSuccess = BulkInsert(table, conn);
                return IsSuccess;
            catch (Exception e)
                logEntry = "";
                logEntry = logEntry + "------------------Error # ----------------";
                logEntry = logEntry + e.Message + "\n";

                //add more error before this line
                logEntry = logEntry + "\n\n";
                File.AppendAllText(logFilePath, logEntry);
                return false;
        }//end InsertRecords

        bool BulkInsert(DataTable source, SqlConnection cn)
                using (var bulk = new SqlBulkCopy(cn))
                    bulk.DestinationTableName = source.TableName;

                                   bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("IP", "IP"));  // DataTable Col Name, DB Table Col Name
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DateTime", "DateTime"));
                    bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("QueryTerm", "QueryTerm"));
                                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("UserRoles", "UserRoles"));
                                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Results", "Results"));

                return true;
            catch (Exception ex)
                return false;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.