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.