How to get the primary key ID of the newly inserted data of the database accurately and efficiently

For example, we have created a new table userinformation, fields as follows ID, primary key, self-increment, other fields name,pwd,Email

Then we'll perform a new insert operation:

INSERT INTO UserInformation
(name,pwd,email) VALUES (' xiaoming ', ' 123 ', ' 111 ')

We want to get the value of the primary key to insert this data when inserting data.

There are two ways to resolve this:

1, one is to enter a field directly, and then query, the code is as follows:

INSERT INTO UserInformation
(name,pwd,email) VALUES (' Xiao Zhang ', ' 123 ', ' 111 ')
SELECT @ @identity

After insertion, but this is not completely accurate.

Insert Into[test1]. [dbo]. [UserInformation]
(name,pwd,email) VALUES (' Xiao Zhang ', ' 123 ', ' 111 ')
SELECT @ @identity

A database lock is involved, and if a database lock is adjusted, there may be errors when there is a large amount of data in parallel when the number level is particularly large.

2, the other method is the output at the same time as the insertion, the code is as follows:

INSERT INTO UserInformation
(name,pwd,email) output inserted. Id values (' xiaoming ', ' 123 ', ' 111 ')

In addition to the primary key, you can also output other fields, accurate and efficient.

It is recommended to use the second

  Insert  into [Db_date_plan].[dbo].[T_userinfo](username,userpassword,useremail) output inserted. Id,inserted.usernameValues('Xiao Ming','12322','11122')Update [Db_date_plan].[dbo].[T_userinfo] SetUserName='XXX'OUTPUT inserted.idWHEREUserPassword='Admin'

Excerpt from: https://www.cnblogs.com/zhangchengye/p/5148545.html

How to get the primary key ID of the newly inserted data of the database accurately and efficiently