Menu
I'm developing a project using VB.NET connected to SQL Server database
and in this project i need to get the value of column called "ID" after inserting a record to the database immediately.
thanx.
281
asked Dec 08 '09 06:12
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
To know the current auto_increment value, we can use the last_insert_id() function. Firstly, we will create a table with the help of INSERT command. Now, we will see how many records have I inserted into my table with the help of SELECT command. Therefore, the last auto increment is 4.
We use SCOPE_IDENTITY() function to return the last IDENTITY value in a table under the current scope. A scope can be a module, trigger, function or a stored procedure. We can consider SQL SCOPE_IDENTITY() function similar to the @@IDENTITY function, but it is limited to a specific scope.
The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add.
CREATE TABLE dbo.SomeTable (
ID int IDENTITY
,[NAME] varchar(50)
);
go
INSERT INTO dbo.SomeTable ([Name])
SELECT 'Joe' UNION
SELECT 'Jim' UNION
SELECT 'JIll'
;
SELECT ident_current('dbo.SomeTable') AS [LastID_1]
,@@IDENTITY AS [LastID_2]
,scope_identity() AS [LastID_3]
;
USES:
ident_current ('TableName') for a specific table, not limited by scope and session@@IDENTITY last ID in current sessionscope_identity() last ID in current session, current scope
186
Have a look at SCOPE_IDENTITY (Transact-SQL)
5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
