How to get the identity of an inserted row?
@@IDENTITYreturns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.SCOPE_IDENTITY()returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.IDENT_CURRENT('tableName')returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."The
OUTPUTclause of theINSERTstatement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.
Safest way to get last record ID from a table
Safest way will be to output or return the scope_identity() within the procedure inserting the row, and then retrieve the row based on that ID. Use of @@Identity is to be avoided since you can get the incorrect ID when triggers are in play.
Any technique of asking for the maximum value / top 1 suffers a race condition where 2 people adding at the same time, would then get the same ID back when they looked for the highest ID.
get new SQL record ID
Thanks all who suggested SELECT SCOPE_IDENTITY(). I was able to create a stored procedure:
USE [dbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsert]
(
@Nn varchar(30)
)
AS
BEGIN TRANSACTION InsertRecord
INSERT INTO A (Nn)
VALUES (@Nn)
SELECT NewID = SCOPE_IDENTITY() -- returns the new record ID of this transaction
COMMIT TRANSACTION InsertRecord
and call the sproc using VB:
Dim strNn '<- var to be passed'
Set cn = Server.CreateObject("ADODB.Connection")
connectString = "DSN"
cn.Open connectString, "user", "PW0rd"
Set rs = Server.CreateObject("ADODB.Recordset")
set rs = cn.Execute("EXEC [dbname].[dbo].[A] @Nn=" & strNn)
'return the value'
resultID = rs(0)
I can now use resultID anytime I refer to the newly created ID.
Getting a recordID in my SQL select query
Probably want to use Row_Number:
Select ROW_NUMBER() OVER(ORDER BY empID) AS RecId,
empID,
EmpFirstName,
EmpLastName
from EMPLOYEES
unless there is actually a RecId column in EMPLOYEES in which case it would just be this:
Select RecId,
empID,
EmpFirstName,
EmpLastName
from EMPLOYEES
Get the new record primary key ID from MySQL insert query?
You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Eg:
INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();
This will get you back the PRIMARY KEY value of the last row that you inserted:
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.
So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.
How to get the ID of new row in SQL?
If your version of SQLite is 3.31.0+ you can define Formatted_Column as a generated column: (VIRTUAL or STORED):
CREATE TABLE "TEST" (
"ID" INTEGER,
"Formatted_Column" TEXT GENERATED ALWAYS AS ('U' || ID) STORED,
PRIMARY KEY("ID" AUTOINCREMENT)
);
After you insert 2 rows:
INSERT INTO TEST (ID) VALUES (NULL), (NULL);
you will have:
ID Formatted_Column 1 U1 2 U2 Getting ID of new record after insert
Run this after your execute statement and before you close your connection:
lsSQL = "SELECT @@IDENTITY AS NewID"
Set loRs = loConn.Execute(lsSQL)
llID = loRs.Fields("NewID").valueI pulled it from here:
http://www.kamath.com/tutorials/tut007_identity.aspIn SQL Server is it possible to get id of a record when Insert is executed?
In .Net at least, you can send multiple queries to the server in one go. I do this in my app:
command.CommandText = "INSERT INTO [Employee] (Name) VALUES (@Name); SELECT SCOPE_IDENTITY()";
int id = (int)command.ExecuteScalar();Works like a charm.
Related Topics
Best Practices for Inserting/Updating Large Amount of Data in SQL Server 2008
Should a Composite Primary Key Be Clustered in SQL Server
Spark Dataframe Nested Case When Statement
Why Is Union Faster Than an or Statement
How to Find the User That Has Both a Cat and a Dog
How to Group by and Return Sum Row in Postgres
Row with Minimum Value of a Column
Select Average from MySQL Table with Limit
Get Records 10 Min Before System Datetime in SQL
Differencebetween Join Keyword and Inner Join Keyword in Oracle SQL
Row_Number() Over Not Fast Enough with Large Result Set, Any Good Solution
How to Use SQL Wildcards in Linq to Entity Framework
How to Determine the Primary Key for a Table in SQL Server
Get Top 10 Products for Every Category
How to Remove Duplicates from Table Using SQL Query
Search All Databases for Value
String Concatenation Operator in Oracle, Postgres and SQL Server