Dashboard > iBATIS DataMapper > Home > Frequently Asked Questions > How do I use an insert statement? cs
How do I use an insert statement? cs
Added by Roberto Rabe, last edited by Roberto Rabe on May 19, 2005  (view change)
Labels: 
(None)


If your database table uses an IDENTITY, AUTO_INCREMENT, or SERIAL column or you have defined a SEQUENCE/GENERATOR, you can use the <selectKey> element in an <insert> statement to use or return that database-generated value.

Examples:

C#
int key = (int)sqlMap.Insert("InsertOrganization", organization);
MS SQL Server
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    <selectKey property="Id" type="post" resultClass="int">
    SELECT @@IDENTITY AS value
    </selectKey>
    INSERT INTO Organizations
    (Org_Code, Org_Name)
    VALUES
    (#Code#, #Name#)
    </insert>

With MS SQL Server, you also have the ability to use a <statement> with a resultClass to return the key:

MS SQL Server using a <statement> with resultClass
<statement id="InsertCategory" parameterClass="Category" resultClass="int">
    insert into Categories
    (Category_Name, Category_Guid)
    values
    (#Name#, #Guid:UniqueIdentifier#);
    select SCOPE_IDENTITY() as value
    </statement>
MySQL
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    <selectKey property="Id" type="post" resultClass="int">
    SELECT LAST_INSERT_ID() AS value
    </selectKey>
    INSERT INTO Organizations
    (Org_Code, Org_Name)
    VALUES
    (#Code#, #Name#)
    </insert>

Note that this Oracle example uses sets the selectKey type="pre" to indicate that the selectKey statement should be executed before the insert statement.

Oracle
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    <selectKey property="Id" type="pre" resultClass="int">
    select seq_orgs.nextval as value from dual
    </selectKey>
    INSERT INTO Organizations
    (Org_Id, Org_Code, Org_Name)
    VALUES
    (#Id#, #Code#, #Name#)
    </insert>

You could choose to use a <statement> like the MS SQL Server example, but you won't be able to retrieve the sequence value:

Oracle using sequence in insert
<statement id="InsertOrganization" parameterClass="Organization">
    INSERT INTO Organizations
    (Org_Id, Org_Code, Org_Name)
    VALUES
    (seq_orgs.nextval, #Code#, #Name#)
    </statement>
PostgreSQL
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    <selectKey property="Id" type="post" resultClass="int">
    SELECT cast(last_value as int) AS value
    FROM organizations_org_id_seq
    </selectKey>
    INSERT INTO Organizations
    (Org_Code, Org_Name)
    VALUES
    (#Code#, #Name#)
    </insert>
DB2
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    {TODO: show sequence usage!}
    </insert>
Firebird
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    {TODO: show generator usage!}
    </insert>
Sybase
<insert id="InsertOrganization" parameterClass="Organization" resultClass="int">
    {TODO: show identity usage!}
    </insert>

Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators