Store the value of output inserted._ID to local variable to reuse it in another query

Question

I want to simply execute a SQL query that would return the current id of the row that was added to a table, example:

insert into V_Post output inserted._ID values('Please return the id of THIS row :)')

That shall returns a number like 155

So the aim of my question is to store that number in some kind of local variable like XVALUE and then use this XVALUE in any other SQL query like:

insert into V_Posts values(1, XVALUE, null, GETDATE(), null, null, 1)

So i thougt of something like:

int XVALUE = insert into V_Post output inserted._ID values('Please return the id of THIS row :)')

insert into V_Posts values(1, XVALUE, null, GETDATE(), null, null, 1)

OR

insert into V_Posts values(1, insert into V_Post output inserted._ID values('Please return the id of THIS row :)'), null, GETDATE(), null, null, 1)

But both it didn't work :(

I hope i explained my question well and i really thank you in advance for your help.

EDIT

Please notice i have two tables: V_posts and V_post

Does this help: [SQL Server - Return value after INSERT](http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) — Claude, Nov 17 '13 at 18:18
Yes it is but Please notice i have two tables: V_posts and V_post, i am trying to get the ID of one table and use it another table — BOSS, Nov 17 '13 at 18:20
Using @@IDENTITY is never a good Idea it will return last Identity Values generated in the current session from any table . Use IDENT_CURRENT('Table_Name') function which will return last generated value for the Table name you pass as parameter — M.Ali, Nov 17 '13 at 18:27
Once you have got the value in a parameter use it anywhere you like insert, update other table whatever you want to do with it. — M.Ali, Nov 17 '13 at 18:28

M.Ali · Accepted Answer · 2019-05-16T12:18:21.873

19

IDENTITY COLUMN

If it is an identity column and you are only inserting a single row then you can use SCOPE_IDENTITY() function to get the last generated Identity value within the scope of the current session.

DECLARE @NewValue INT;
insert into V_Post 
values('Please return the id of THIS row :)')
SET @NewValue = SCOPE_IDENTITY()

IF NOT IDENTITY Column Or Multiple Identity values

If it is an identity column and you are inserting multiple rows and want to return all the newly inserted Identity values, or it is not an identity column but you need the last inserted value then you can make sure of OUTPUT command to get the newly inserted values.

DECLARE @tbl TABLE (Col1 DataType)
DECLARE @NewValue Datatype;

insert into V_Post 
output inserted._ID INTO @tbl
values('Please return the id of THIS row :)')

SELECT @NewValue = Col1 FROM @tbl

edited May 16 '19 at 12:18

answered Nov 17 '13 at 18:25

M.Ali

67,945
13
101
127

@MartinSmith - I would be glad to see another solution then. – Yosi Dahari Nov 17 '13 at 18:31
1

@Yosi @@IDENTITY returns the last Identity Values generated in the current session , which is error prone, you could be doing inserts in multiple tables at a time, using IDENT_CURRENT eliminates this risk – M.Ali Nov 17 '13 at 18:31
@muhammed but that can return an identity value generated by another concurrent session. – Martin Smith Nov 17 '13 at 18:34
@MartinSmith hmmmm you are right there what do you suggest than ? – M.Ali Nov 17 '13 at 18:36
`scope_identity` or `output`. – Martin Smith Nov 17 '13 at 18:37
1

@MartinSmith cheers pal will keep this in mind, this never occurred to me . anyway you learn something new everyday :) – M.Ali Nov 17 '13 at 18:38

Yosi Dahari · Answer 2 · 2013-11-17T22:43:09.630

1

You can use SCOPE_IDENTITY():

insert into V_Posts values(1, SCOPE_IDENTITY(), null, GETDATE(), null, null, 1)

It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value.

You can find here some detailed information about what this value means

edited Nov 17 '13 at 22:43

answered Nov 17 '13 at 18:16

Yosi Dahari

6,794
5
24
44

Thank you for your answer but Please notice i have two tables: V_posts and V_post. I am not trying to get the current row number for the same table. – BOSS Nov 17 '13 at 18:19
Look into the answer in depth, it DOESN'T talk about row number, and doesn't relate to a transaction with one table. – Yosi Dahari Nov 17 '13 at 18:22

Sheriff · Answer 3 · 2020-01-10T03:43:25.983

You can not store inserted.Id value directly into a variable, you must need to declare table variable, than from table variable you can store into a variable

For eg:

-- checking existence of table
IF OBJECT_ID ('Books', 'U') IS NOT NULL
DROP TABLE dbo.Books;

-- creating table
CREATE TABLE dbo.Books
(
  BookID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  BookTitle nvarchar(50) NOT NULL,
  ModifiedDate datetime NOT NULL
);

-- declaring variable
DECLARE @bookId INT;

INSERT INTO Books 
OUTPUT SET @bookId= INSERTED.BookID  
VALUES( 'One Hundred Years of Solitude', GETDATE());

Above insert query will give the error, because you can't store the value directly into a variable.

--Declaring table variable
DECLARE @InsertOutput TABLE
(
  BookID INT
)

INSERT INTO Books 
OUTPUT INSERTED.BookID 
INTO @InsertOutput
VALUES( 'One Hundred Years of Solitude', GETDATE());

SELECT @bookId = BookID FROM @InsertOutput;

SELECT @bookId bookId

Now above @bookId variable you can use where ever you want to use.

If you don't want to use inserted then you can use Scope_identity(), and can store value directly into a variable, but some time using of scope_identity() might be issue of performance.

Kindly,

may I ask, so the Column name on @InsertOuput should be the same as the Column name in INSERTED? If I want to use multiple INSERTED, just use the same name of the column right? — Rian, Apr 26 '20 at 14:22

Store the value of output inserted._ID to local variable to reuse it in another query

3 Answers3