CODEWITHSUNDEEP
c#sql-server
Awadhendra
Awadhendra

Reputation: 513

Bulk query insert in c#

I am inserting record using bulk query from source table to destination table. Source table have 10,000 records. Suppose source table have a column sid int, sname varchar(60) and destination column have sid int, sname varchar(30).

Now I was not able to insert all record successfully, as length problem in source sname and destination sname. As only few rows have a problem.

Now my question is that is there any way to insert record in destination table using bulk insert so that correct record is inserted and incorrect record is not inserted.

I am using c# 3.5

code which I am using

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(constring, SqlBulkCopyOptions.UseInternalTransaction))
        {
            try
            {
                //Edit mapping.
                bulkCopy.ColumnMappings.Add("AddressID", "AddressID");
                bulkCopy.ColumnMappings.Add("AddressLine1", "AddressLine1");
                bulkCopy.ColumnMappings.Add("City", "City");

                //specify destination table.
                bulkCopy.DestinationTableName = "[Address]";

                bulkCopy.BatchSize = 100;
                bulkCopy.NotifyAfter = 100;
               // bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.WriteToServer(table);

            }
            catch (Exception ex)
            {
                richTextBox1.AppendText("\n\n" + ex.Message);
            }
        }

Thanks.

Upvotes: 2

Views: 1943

Answers (1)

user596075
user596075

Reputation:

Are you utilizing BULK INSERT through SQL Server, or are you using the System.Data.SqlClient.SqlBulkCopy class?

If you are using the BULK INSERT T-SQL, then you can just set MAXERRORS equal to whatever your desireble threshold is (the default is 10, which is why your `BULK INSERT is probably canceling out).

BULK INSERT YourDb.YourSchema.YourTable
   FROM 'c:\YourFile.txt'
   WITH 
      (
         FIELDTERMINATOR =' |',
         ROWTERMINATOR =' |\n',
         MAXERRORS = 1000        -- or however many rows you think are exceeding the 30 char limit
      )

EDIT

After seeing your are using the SqlBulkInsert class, I think your best bet would be to modify the DataTable before calling the SqlBulkCopy.WriteToServer() method:

    foreach(DataRow row in table.Rows)
    {
        if (row["YourColumnThatExceeds30Chars"].ToString().Length > 30)
            row["YourColumnThatExceeds30Chars"] = 
                row["YourColumnThatExceeds30Chars"].ToString().Substring(0, 30);
    }

Upvotes: 2

Related Questions