Insert Then Select Inserted

Question

i have stored procedure
a wnat to insert into table
and after insert i want to select the inserted id because it is identity

Answer 1

do you need all of the inserted value or the value of the identity ?

you can use the OUTPUT clause if you are using SQL 2005/2008

example

insert into yourtable ( < column list > )
output inserted.*
select  10, 20, 30

---

KH Tan

Answer 2

Or you can use SCOPE_IDENTITY to get the last value inserted to an identity column:

DECLARE @Table TABLE (Id INT IDENTITY(1,1), Dt DATETIME)

INSERT INTO @Table
SELECT GETDATE()

SELECT SCOPE_IDENTITY()

---

every day is a school day

Answer 3

i want the identity only

Answer 4

you can also use scope identity

check this

declare @tbl table(id int identity(1, 1), val nvarchar(100))
declare @id int

insert into @tbl
select 'abc'

select @id = SCOPE_IDENTITY()
select @id

insert into @tbl
select 'xyz'

select @id = SCOPE_IDENTITY()
select @id