Use @ IDENTITY

Basic knowledge
1. The IDENTITY column cannot be directly updated by the user. It is automatically maintained by the system.
2. The data type of this column must be numeric: int, smallint, tinyint, decimal or numeric with scale 0.
3. This column cannot be null.
4. You cannot set the default value in this column.
5. The increment value can only be an integer (for example, 1, 2,-3 ). It cannot be a decimal or 0.
6. The base value (seed) can be set by the user. The default value is 1.
Understanding @ IDENTITY
@ IDENTITY returns the value of the last inserted IDENTITY. These operations include INSERT, select into, or bulk copy. If a record is inserted to other tables without the IDENTITY column, the system sets it to null. If multiple rows of records are inserted into the IDENTITY table, @ IDENTITY indicates the last generated value. If a trigger is triggered and the trigger inserts data to another table with an IDENTITY column, @ IDENTITY returns the value generated by the trigger. If the table inserted by this trigger does not contain the IDENTITY column, @ IDENTITY is null. If the insert operation fails, the @ IDENTITY value will still increase, so IDENTITY does not guarantee data continuity.
@ IDENTITY is the global variable of the current connection and is only valid for the current connection. That is to say, if the connection is closed and then reconnected, @ IDENTITY is null. In ADO, @ IDENTITY makes sense when the Connection object is opened or closed, that is, it is valid within the scope of the Connection object. In the MTS component, from opening a Connection to an explicit Close Connection (Connection. Close) or before calling SetAbort or SetComplete, @ IDENTITY makes sense during this period.
The Truncate table statement causes the IDENTITY column to start computing again.
Obtain the value of @ IDENTITY.
There are three methods (the following code uses VBScript)
Method 1:
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 2 (limited to ADO 2.0 or above ):
Dim Conn, strSQL, Rs