Use SQL Server to obtain the ID of the inserted record (automatic number)

Source: Internet
Author: User

Recently, I encountered a problem in the development project, that is, after inserting a record, I need to immediately obtain the ID in the database, which is auto-incrementing. How can I do this?There are several methods available in SQL server 2005.
The simplest way to obtain this ID is to select @ indentity after the query.
-- SQL statement to create databases and tables
Copy codeThe Code is as follows:
Create database dbdemo
Go
Use dbdemo
Go
Create table tbldemo
(
Id int primary key identity (1, 1 ),
Name varchar (20)
)
Go

-- Execute the following SQL statement to check the values of the auto-incrementing column corresponding to the inserted record.
Insert into tbldemo values ('test') select @ identity
SQL Server 2000 has three similar functions: SCOPE_IDENTITY, IDENT_CURRENT, and @ IDENTITY. They both return values inserted into the IDENTITY column.

1) IDENT_CURRENT returns the final id value generated for any session and a specific table in any scope. It is not restricted by the scope and session, but by the specified table.
2) @ IDENTITY returns the final id value generated for any table in all scopes of the current session.
3) 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.