Capturing multiple IDENTITY values after inserting into SQL Server table

Description:-

In this example we explain that how to fetching or gets multiple IDENTITY values after inserting record in sql server table.

Generally we have a situation where we need get IDENTITY of the last inserted record and this is simply achieved by using SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() functions provide by sql server.

But when we want to capturing multiple IDENTITY values after inserting multiple record at that time this all function will not satisfy our condition.so  how to achieve this follow the below post.

insert into demo(name) values('pavan')

you can easily fetch this last inserted  record id by following query.

SELECT @@IDENTITY

But after inserting multiple record how can you fetch or capture the IDENTITY of the last inserted records.

select * from demo

then fire this query like

DECLARE @DEMOTABLE TABLE ([id] INT,[name] VARCHAR(100));

INSERT INTO [DBO].[Demo]

(

[name]

)

OUTPUT INSERTED.[id],INSERTED.[name]

INTO @DEMOTABLE ([id],[name]) select name from demo

Now if you want to fetch all IDENTITY that inserted at last query then you can easily fetch by using this query.

SELECT [id],[name] FROM @DEMOTABLE

when you fire above query it will return Id 9 and 10 both.