2

So I am trying to do a batch insert using MyBatis below is my xml mapper

<insert id="insertMessages" parameterType="java.util.List">
    INSERT INTO Messages(user, id, month,
    key, value, name,
    message, dateCreated, createdBy)
    VALUES
    <foreach item="item"  collection="list" open="(" separator="),("  close=")">
                #{item.user},
                #{item.id},
                #{item.month},
                #{item.key},
                #{item.value},
                #{item.name},
                #{item.message},
                GETDATE(),
                #{item.createdBy}
    </foreach>

</insert>

right now it only works when the batch size is 200 or less. Anything more than 200, gets the error :

-The incoming tabular data stream(TDS) remote procedure call (RPC) protocol stream is incorrect.Too many parameters provided in the RPC request.The maximum is 2100

which is caused by SQL server not being able to take more than 2100 paramerters and myBatis builds query with "?" and then passes parameters to the query. In my case it is 8 parameters so if batch is 300 then 2400 parameters will be passed to DB. So I can keep adjusting the batch insert size for inserts but there's probably a better way. What can I do to not hit this parameters limitation on SQL server side? without changing SQL server. Thanks!

aschipfl
  • 33,626
  • 12
  • 54
  • 99
raging_russian
  • 96
  • 7
  • 1
    Looks like you are inserting 9 parameters. Look for SQL Bulk Insert, that should help. – Chuck May 12 '16 at 16:29
  • Thanks @Chuck but as far as I understand Bulk Insert is usually used for inserting files, in my case I am inserting values into the table through java without use of any files, how is that supposed to be done with Bulk Insert? – raging_russian May 13 '16 at 15:47
  • 1
    Can you try to split the inserts into a bunch of smaller inserts, like maybe just 100 rows at a time? – Chuck May 13 '16 at 16:23
  • yes, I can do that but the application I am building requires higher efficiency and many batch inserts with more than 9 parameters per row. So I don't want to always calculate the max size of the batch insert if possible. – raging_russian May 13 '16 at 17:05
  • This is not batch insert. This is one huge sql statement and you hit a limit of the parameters in it. Search in SO for 'mybatis batch insert' you'll find several answers that will give you a hint like this http://stackoverflow.com/questions/23486547/mybatis-batch-insert-update-for-oracle – Roman-Stop RU aggression in UA May 18 '16 at 16:48
  • @RomanKonoval well looks like this is exactly what i am doing with and using – raging_russian May 23 '16 at 17:43
  • Answer accepted by OP likely matched their needs, but it does not mean it is to be recommended way. Check [this answer and its comments](http://stackoverflow.com/a/29264696/7076047) on the same question: the key is executorType (reuse or Batch). – blackwizard Mar 29 '17 at 10:06

0 Answers0