MSSQL 2008 batch insert using iterate element

[indevnow]

Hi,

I would like to be able to insert multiple records into a MSSQL 2008
table using the following query format:

INSERT INTO [ITEM] (Name, Type, Description)
VALUES (“Gummy”, “Candy”, “taste sweet”), (“Lemon”, “Fruit”, “taste
sour”)

I tried the following:
<insert id="InsertItemCollection"
parameterClass="ItemFactory.models.Contract.ItemContextBatch" >
INSERT INTO [User] (Name, Type, Description)
VALUES
<iterate property="ItemContextCollection"
open="(" close=")" conjunction=",">
#ItemContextCollection [].ItemContext.Name#,
#ItemContextCollection [].ItemContext.Type#, #ItemContextCollection
[].ItemContext.Description#
</iterate>

</insert>

However, this complains that I don’t have the same number of values as
columns:

"There are fewer columns in the INSERT statement than values specified
in the VALUES clause. The number of values in the VALUES clause must
match the number of columns specified in the INSERT statement."

The query works fine when I execute it from MSSQL server management
studio. Am I note using the iterate tag correct?

Sincerely,

indevnow

[in dev]

I wonder if my problem is the provider that I am using. I just copied the sqlServer2005 provider example and modified it a bit. If it is not using the correct provider than it may not understand the syntax for passing multiple records in a single insert statement. Below is a copy of my provider configuration:

<provider
     name="sqlServer2008"
     enabled="true"
     default="true"
     description="Microsoft SQL Server, provider V4.0.0.0 in framework .NET V4.0"
     assemblyName="System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
     connectionClass="System.Data.SqlClient.SqlConnection"
     commandClass="System.Data.SqlClient.SqlCommand"
     parameterClass="System.Data.SqlClient.SqlParameter"
     parameterDbTypeClass="System.Data.SqlDbType"
     parameterDbTypeProperty="SqlDbType"
     dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
     commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
     usePositionalParameters = "false"
     useParameterPrefixInSql = "true"
     useParameterPrefixInParameter = "true"
     parameterPrefix="@"
     allowMARS="true"
    />

 

Sincerely,

indevnow.

[in dev]

I resolved the issue. The problem was in my insert statement; I didn't understand how open and closwe were working. I thought that it was per iteration, not for the whole code block. This fixes the issue:

<insert id="InsertItemCollection"
parameterClass="ItemFactory.models.Contract.ItemContextBatch" >
        INSERT INTO [User] (Name, Type, Description) VALUES

        <iterate prepend = " " property="ItemContextCollection"
           open=" " close=" " conjunction=",">

           #ItemContextCollection [].ItemContext.Name#,
#ItemContextCollection [].ItemContext.Type#,  #ItemContextCollection
[].ItemContext.Description#
        </iterate>

     </insert>

 

indevnow

[丰刘]

you can debug within SQLProfiler to monitor what's the SQL been executed.

Liu Feng

[Ron Grabowski]

Try using the <update> statement. I recall <insert> not always working the way I wanted it to.

---

From: 丰刘 <bli...@gmail.com>
To: mybatis...@googlegroups.com
Sent: Thursday, April 7, 2011 7:57 PM
Subject: Re: MSSQL 2008 batch insert using iterate element

[Edgar Madrigal]

Maybe you can use:

INSERT with SELECT UNION, like this:

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

This is an example:

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/ 

Also I know that at least on sql server you can create a new custom type and use it as a table, like this:

http://msdn.microsoft.com/en-us/library/bb675163.aspx

--Here you create the custom type as table.

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

--You can use it like this

CREATE PROCEDURE usp_UpdateCategories 
    (@tvpNewCategories dbo.CategoryTableType READONLY)

The parameter must be readonly.

I know on ADO.Net you can map that parameter into a dataset, I am not sure if this is supported by mybatis, but at least you cabn give it a try.

2012/5/16 Ron Grabowski <rongra...@yahoo.com>