[Reprint] T-SQL: insert and return auto-incrementing ID

Source Address: http://www.cnblogs.com/lsjwzh/archive/2008/08/03/1259370.html

SQL Server 2000 has three similar functions: scope_identity, ident_current, and @ identity. Both return values inserted into the identity column.

Ident_current returns the last generated id value for any session and specific tables in any scope. Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the value generated for a specific table in any session and scope.
@ Identity returns the last generated id value for any table in all scopes of the current session.
Scope_identity returns the last generated id value for the current session and any table in the current scope.

Scope_identity and @ identity return the last id value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.

For example, there are two tables T1 and T2, and an insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.

Assume that both T1 and T2 have the identity column. @ identity and scope_identity return different values at the end of the insert Statement on T1.

@ Identity returns the value of the last identity column inserted to any scope in the current session. This value is the value inserted in T2.

Scope_identity () returns the identity value inserted in T1, which is the last insert that occurs in the same scope. If the scope_identity () function is called when an insert statement occurs in the scope before the identifiers column, the function returns NULL.

The values returned by ident_current ('t1') and ident_current ('t2') are the last auto-increment values of the two tables.

Ajqc's experiment: (40 local threads, 40 + 40 remote threads are tested concurrently and W rows are inserted). The conclusion is:
1. in typical cascade applications. @ identity cannot be used. When there are more than 256 rows on cii850, m sd machines, concurrency conflicts will occur. on p42.8c and 512 m ddr, A concurrency conflict occurs when more than 6000 rows exist.
2. scope_identity () is absolutely reliable and can be used in the stored procedure without the need to create a trigger and avoid concurrent conflicts.

Select ident_current ('tablename') -- returns the last tag value generated in the specified table.
Select ident_incr ('tablename') -- returns the increment value of the marked field in the specified table.
Select ident_seed ('tablename') -- returns the seed value of the marked field in the specified table.

Returns the automatic number of the last inserted record.
Select ident_current ('tablename ')
Return the next automatic number:
Select ident_current ('tablename') + (select ident_incr ('tablename '))

Select @ identity -- returns the last tag value generated in all tables of the current session.

Returns the id value when inserting data.

 

When I was working on a project a few days ago, when I encountered the problem of inserting data and returning the id value, I recorded the following:

Returns the SQL statement of the ID value when inserting data.

MSSQL:

Insert into Test2 (AA) values ('cc') Select @ identity as sequence

MySQL:

Select last_insert_id () as ID from t_d_nm_shop_templet limit 1

In ibatis, you can configure it as follows:

<! -- Create a task MySQL database. In the MySQL database, the task_id in the t_d_qt_task table is an auto-increment field. -->
<! -- When inserting data, the configuration file of the inserted value of task_id is returned as follows -->
<Parametermap id = "maptask" class = "Java. util. hashmap">
<Parameter property = "taskgroup_id" jdbctype = "varchar"
Javatype = "Java. Lang. Integer" mode = "in"/>
<Parameter property = "task_name" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "task_content" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "test_object" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "status" jdbctype = "varchar"
Javatype = "Java. Lang. Integer" mode = "in"/>
<Parameter property = "user_id" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "script_type" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
</Parametermap>
<Insert id = "inserttask" parametermap = "maptask">
Insert into t_d_qt_task ('taskgroup _ id', 'Task _ name'
, 'Task _ content', 'test _ object', 'priority ', 'status', 'user _ id'
, 'Script _ type') values (?,?,?,?, '9 ',?,?,?)
<Selectkey resultclass = "Java. Lang. String" keyproperty = "TID">
Select last_insert_id () as task_id from t_d_qt_task limit 1
</Selectkey>
</Insert>
<! -- Create a task Oracle database. In the Oracle database, the task_id in the t_d_qt_task table is not set to auto-increment. -->
<! -- (In this way, the returned value of the primary key is always greater than the actual inserted value.) cancel the association between task_id and the trigger. -->
<! -- Directly create a seq: seq_d_qt_task to obtain its return value and insert it directly into the table. Pay attention to the configuration -->
<! -- Keyproperty = "task_id ". Is to put the return value of seq into the task_id of the corresponding ing -->
<Parametermap id = "maptask" class = "Java. util. hashmap">
<Parameter property = "task_id" jdbctype = "varchar"
Javatype = "Java. Lang. Integer" mode = "in"/>
<Parameter property = "taskgroup_id" jdbctype = "varchar"
Javatype = "Java. Lang. Integer" mode = "in"/>
<Parameter property = "task_name" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "task_content" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "test_object" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "status" jdbctype = "varchar"
Javatype = "Java. Lang. Integer" mode = "in"/>
<Parameter property = "user_id" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
<Parameter property = "script_type" jdbctype = "varchar"
Javatype = "Java. Lang. String" mode = "in"/>
</Parametermap>
<Insert id = "inserttask" parametermap = "maptask">
<Selectkey resultclass = "Java. Lang. Integer"
Keyproperty = "task_id">
Select seq_d_qt_task.nextval as ID from dual
</Selectkey>
Insert into t_d_qt_task (task_id, taskgroup_id, task_name
, Task_content, test_object, priority, status, user_id
, Script_type) values (?,?,?,?,?, '9 ',?,?,?)
</Insert>