15

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

Improve this question
5
  • 2
    Does this help: SQL Server - Return value after INSERT Commented Nov 17, 2013 at 18:18
  • 1
    Is this an Identity Column ??? Commented Nov 17, 2013 at 18:20
  • 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 Commented Nov 17, 2013 at 18:20
  • 1
    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 Commented Nov 17, 2013 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. Commented Nov 17, 2013 at 18:28

3 Answers 3

Reset to default
25

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@MartinSmith - I would be glad to see another solution then.
@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
@muhammed but that can return an identity value generated by another concurrent session.
@MartinSmith hmmmm you are right there what do you suggest than ?
@MartinSmith cheers pal will keep this in mind, this never occurred to me . anyway you learn something new everyday :)
|
2

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

Improve this answer

2 Comments

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.
Look into the answer in depth, it DOESN'T talk about row number, and doesn't relate to a transaction with one table.
0

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,

Improve this answer

2 Comments

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 - No, only data type should be same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.