SQL Server - Best way to get identity of inserted row?
@@IDENTITY
returns 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
OUTPUT
clause of theINSERT
statement 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.
Get the last inserted row ID (with SQL statement)
If your SQL Server table has a column of type INT IDENTITY
(or BIGINT IDENTITY
), then you can get the latest inserted value using:
INSERT INTO dbo.YourTable(columns....)
VALUES(..........)
SELECT SCOPE_IDENTITY()
This works as long as you haven't inserted another row - it just returns the last IDENTITY
value handed out in this scope here.
There are at least two more options - @@IDENTITY
and IDENT_CURRENT
- read more about how they works and in what way they're different (and might give you unexpected results) in this excellent blog post by Pinal Dave here.
Select id from last inserted row
You can use the SCOPE_IDENTITY()
in your stored procedure to get last inserted record id.
SELECT SCOPE_IDENTITY() :- It will returns the last identity value inserted into an
identity column in the same scope.
SELECT @@IDENTITY :- @@IDENTITY will return the last identity value entered
into a table in your current session.
SELECT IDENT_CURRENT(‘tablename’) :- IDENT_CURRENT is not limited by scope and
session; it is limited to a specified table.
There are other options also such as
1. Select Top 1 id From Table Order by id desc
2. SELECT max(id) FROM table
Refer msdn http://msdn.microsoft.com/en-us/library/ms190315.aspx
and
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
SQL Get the row number of the inserted row
- It is an error to think that rows have numbers unless an
ORDER BY
clause is included. - The only way to find a row after you have inserted it is to search for it. Presumably your table has a primary key; use that to search for it.
Oracle get id of inserted row with identity always
The equivalent is
INSERT INTO dummy_schema.names (name) VALUES ('Random')
RETURNING id INTO :myvalue;
The mechanism how to pick up the returned ID depends on the host language (Java, PL/SQL, SQL*Plus etc).
How do I get inserted row Id, along with related data back after insert without inserting the related data
@PeterHe gave me an idea on how to solve this with MERGE
Got it working as follows. When all rows have been inserted I can query @temp to continue the rest of the inserts.
DECLARE @temp AS TABLE(
[action] NVARCHAR(20)
,[GlobalId] BIGINT
,[Personnumber] NVARCHAR(100)
,[Firstname] NVARCHAR(100)
,[Lastname] NVARCHAR(100)
);
;WITH person AS
(
SELECT top 1
t.[Personnumber]
,t.[Firstname]
,t.[Lastname]
FROM [temp].[RawRoles] t
WHERE t.Personnumber NOT IN
(
SELECT i.Account FROM [security].[Accounts] i
)
)
MERGE [security].[Identities] AS tar
USING person AS src
ON 0 = 1 -- all rows from src need to be inserted, ive already filtered out using CTE Query.
WHEN NOT MATCHED THEN
INSERT
(
[Created], [Updated]
)
VALUES
(
GETUTCDATE(), GETUTCDATE()
)
OUTPUT $action, inserted.GlobalId, src.[Personnumber], src.[Firstname], src.[Lastname] INTO @temp;
SELECT * FROM @temp
Related Topics
How to Find a Table Having a Specific Column in Postgresql
Adding a New SQL Column with a Default Value
How to Cast a String to Integer and Have 0 in Case of Error in the Cast with Postgresql
Postgresql: Insert into ... (Select * ...)
Benefits of Using SQL Ordinal Position Notation
Comma Separated Values with SQL Query
How to Show Row Numbers in Postgresql Query
How to Get N Rows Starting from Row M from Sorted Table in T-Sql
Boolean VS Tinyint(1) for Boolean Values in MySQL
How to Create a Date in SQL Server Given the Day, Month and Year as Integers
Oracle - How to Create a Materialized View with Fast Refresh and Joins
Postgres Error: More Than One Row Returned by a Subquery Used as an Expression
SQL Server Select Where Any Column Contains 'X'
Get Execution Time of Postgresql Query
How to Do Multiple Case When Conditions Using SQL Server 2008