getting the autoincremnt value after inserting record using SQL Server

Question

Hi Guys

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.

Answer 1

Have a look at SCOPE_IDENTITY (Transact-SQL)

Answer 2

INSERT
    INTO [News]
    (
     LanguageID,
     Title,
     Short,
     [Full],
     Published,
     AllowComments,
     CreatedOn
    )
    VALUES
    (
     @LanguageID,
     @Title,
     @Short,
     @Full,
     @Published,
     @AllowComments,
     @CreatedOn
    )

    set @NewsID=@@identity

Answer 3

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 session
• scope_identity() last ID in current session, current scope