Multiple row insert -> statement exceeds the maximum allowed number of 1000 row values.

Question

I'm using sql 2008 express edition and I'm trying to do a multiple row insert thru my C# application.

I've got around 100000 records that needs to be inserted.

Okay all goes well for the first 1000 records then I'm getting the error:

"The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values."

I looked at my column data type -> int, so that shouldn't be the problem. I checked my code and I'm inserting in steps of 500 records.

So I googled it but couldn't find anything useful. Can somebody explain why I get this error and if possible how to solve it.

Answer 1

You can use the SQLBulkCopy class. Which supports batching, transactions and is more efficient than standard insert statements.

Answer 2

this is how my code handles the multi insert

var count = "SELECT COUNT(*) as rowcount FROM table_mysql GROUP BY id";

var countReader = Retrieve(count);
var countRows = 0;
try
{
    while (countReader.Read())
    {
        countRows += int.Parse(countReader.GetValue(0).ToString());
    }
}
catch (Exception ex)
{
    Log.LogMessageToFile("Import.cs -> table_mssql: " + ex.StackTrace + " /n" + ex.Message);
}
finally
{
    if (countReader != null) { countReader.Close(); _crud.close_conn(); }
}

for (var a = 0; a < countRows; )
{
    var sql = "SELECT id, traffic_id, dow, uu, imps, impsuu, otsw, otsm FROM table_mysql LIMIT " + a + ", " + (a + 500) + "";

    var reader = Retrieve(sql);
    try
    {
        var builder = new StringBuilder();
        builder.Append(
            "SET IDENTITY_INSERT table_mssql ON;INSERT INTO table_mssql(id, traffic_id, dow, uu, imps, impsuu, otsw, otsm) VALUES ");
        while (reader.Read())
        {
            Application.DoEvents();
            try
            {
                builder.Append("(" + reader.GetValue(0) + ", " + reader.GetValue(1) + ", " +
                               reader.GetValue(2) +
                               ", " + reader.GetValue(3) + ", " + reader.GetValue(4) +
                               ", " + reader.GetValue(5) + ", " + reader.GetValue(6) + ", " +
                               reader.GetValue(7) +
                               "), ");

            }
            catch (Exception ex)
            {
                Log.LogMessageToFile("Import.cs -> table_mssql: " + ex.StackTrace + " /n" + ex.Message);
            }
        }

        var sqlDB = builder.ToString(0, builder.Length - 2);

        sqlDB += ";SET IDENTITY_INSERT table_mssql OFF;";
        if (!InsertDB(sqlDB))
        {
            Log.LogMessageToFile("Import.cs -> table_mssql: No insert happend!");
        }
    }
    catch (Exception ex)
    {
        Log.LogMessageToFile("Import.cs -> table_mssql: " + ex.StackTrace + " /n" + ex.Message);
        return false;
    }
    finally
    {
        if (reader != null)
        {
            reader.Close();
            _crud.close_conn();
        }
    }
    a = a + 500;
}

I'm going to check the sqlbulkcopy. Maybe that is a better solution.

Answer 3

You can take that whole thing down to this:

var sql = "SET IDENTITY_INSERT table_mssql ON;" 
        + "INSERT INTO table_mssql"
        +      "(id, traffic_id, dow, uu, imps, impsuu, otsw, otsm)"
        + " SELECT id, traffic_id, dow, uu, imps, impsuu, otsw, otsm "
        + " FROM table_mysql;"
        + "SET IDENTITY_INSERT table_mssql OFF;";

if (!InsertDB(sqlDB))
{
    Log.LogMessageToFile("Import.cs -> table_mssql: No insert happend!");
}

Also: you should know that Sql Server doesn't support MySql's LIMIT keyword. It uses TOP or ROW_NUMBER instead.