The SELECT @ @Identity returns the value of the Automatically incremented field.

1. IDENTITY column can not be directly updated by the user, it is automatically maintained by the system.

2. The column data type must be of numeric value: int, smallint, tinyint, decimal or numeric with scale 0.

3. The column cannot be null.

4. You cannot set a default value on this column.

5. The increment can only be cosmetic (for example: 1,2,-3). cannot be a decimal, nor can it be 0.

6. The base value (seed value seeds) can be set by the user and the default value is 1.

Understanding @ @IDENTITY

@ @IDENTITY Returns the value of the last insert IDENTITY, which includes insert, SELECT into, or bulk copy. If you insert a record in another table that does not have an IDENTITY column, the system resets it to null. If more than one row of records is inserted into the IDENTITY table, the @ @IDENTITY represents the last resulting value. If a trigger is triggered and the trigger performs an insert operation to another table with an IDENTITY column, @ @IDENTITY returns the value generated by the trigger. If this trigger inserts a table that does not contain an IDENTITY column, then the @ @IDENTITY will be null. If the insert operation fails, the @ @IDENTITY value still increases, so IDENTITY does not guarantee the continuity of the data.

@ @IDENTITY is the global variable for the current connection and is valid only for the current connection. That is, if you disconnect and reconnect, the @ @IDENTITY is null. In the case of ADO, the @ @IDENTITY is meaningful when the Connection object is open and closed, which is valid within the scope of the Connection object. In an MTS component, the @ @IDENTITY meaningful during this period, either from an open connection to an explicit close connection (connection.close) or to a call to Setabort,setcomplete.

Using the Truncate table statement causes the IDENTITY column to restart the calculation.

Get the value of @ @IDENTITY

There are three ways (use VBScript for the following code)

Method One:

Dim Conn, strSQL, Rs
Set Conn = CreateObject ("ADODB. Connection ")
' Open a connection to the ' database
Conn.Open ("DSN=MYDSN; Uid=myuid; pwd=mypwd; ")

' Insert a new record into the table '
strSQL = "INSERT into mttable (columnName) VALUES (' Something ')"

' Execute the SQL statement
Conn.execute (strSQL)

' Get the @ @IDENTITY.
strSQL = "SELECT @ @IDENTITY as NewID"
Set Rs = Conn.execute (lssql)
NewID = Rs.fields ("NewID"). Value

' Close the connection
Conn.close ()
Set Conn = Nothing

Method Two (only ADO 2.0 or above):

Dim Conn, strSQL, Rs
Set Conn = CreateObject ("ADODB. Connection ")
' Open a connection to the ' database
Conn.Open ("DSN=MYDSN; Uid=myuid; pwd=mypwd; ")

' Insert a new record into the table '
Lssql = "INSERT into myTable (columnName) VALUES (' Something ');" &_
"SELECT @ @IDENTITY as NewID;"

' Execute the SQL statement
Set Rs = Conn.execute (lssql)

' Get the second resultset into a RecordSet object
Set Rs = Rs.nextrecordset ()

' Get the inserted ID
NewID = Rs.fields ("NewID"). Value

' Close the connection
Conn.close ()
Set Conn = Nothing

Method Three:

Dim Conn, strSQL, Rs
Set Conn = CreateObject ("ADODB. Connection ")
' Open a connection to the ' database
Conn.Open ("DSN=MYDSN; Uid=myuid; pwd=mypwd; ")

' Insert a new record into the table '
strSQL = "SET NOCOUNT on;" &_
"INSERT into MyTable (columnName) VALUES (' Something ');" &_
"SELECT @ @IDENTITY as NewID;"

' Execute the SQL statement
Set Rs = Conn.execute (lssql)

' Get the inserted ID
NewID = Rs.fields ("NewID"). Value

' Close the connection
Conn.close ()
Set Conn = Nothing