getting the autoincremnt value after inserting record using SQL Server

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 开发者_Python百科"ID" after inserting a record to the database immediately.

thanx.

---

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

---

Have a look at SCOPE_IDENTITY (Transact-SQL)

---

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

    set @NewsID=@@identity

---

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

---

TABLE

EMPID ( AUTOINCREAMENT)
NAME VARCHAR(50)

INSERT INTO TABLE ( NAME) VALUES ('RAMKUMAR')
SELECT @@IDENTITY

use @@identity returns the autoincreament value

---

You can create temp table variable and create a column called StuId.

--Student table creation
CREATE TABLE [dbo].[StuTable](
    [StuId] [int] IDENTITY(1,1) NOT NULL,
    [StuName] [nvarchar](50) NOT NULL,
    [DOB] [date] NULL
)

DECLARE @TempTable TABLE(StuId INT)

INSERT INTO [dbo].[StuTable]([StuName]
                            ,[DOB])
OUTPUT inserted.StuId INTO @TempTable
VALUES ('Peter', '1979-01-01')

SELECT StuId FROM @TempTable --Get the auto increment Id