+ Reply to Thread
Results 1 to 3 of 3

Thread: Bulk query insert in c#

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    34

    Bulk query insert in c#

    I want to insert records using bulk query from source table to destination table. i have around 10,000 records in my source table . Suppose source table have a column sid int, sname varchar(60) and destination column have sid int, sname varchar(30).

    I am not able to insert all record successfully, as length problem in source sname and destination sname. the problem exist only on few rows .

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

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    30
    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).
    Code:
    BULK INSERT Db.Schema.Table
       FROM 'c:\Filename.extension'
       WITH 
          (
             FIELDTERMINATOR =' |',
             ROWTERMINATOR =' |\n',
             MAXERRORS = 1000        -- or however many rows you think are exceeding the 30 char limit
          )
    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:
    Code:
        foreach(DataRow row in table.Rows)
        {
            if (row["ColumnThatExceeds30Chars"].ToString().Length > 30)
                row["ColumnThatExceeds30Chars"] = 
                    row["ColumnThatExceeds30Chars"].ToString().Substring(0, 30);
        }

  3. #3
    Junior Member
    Join Date
    Jan 2012
    Posts
    34
    it works now , thanks a lot .

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts