OUTPUT CLAUSE (Transact-SQL)

How to know your INSERT UPDATE or DELETE statement effect how much recoreds? Or what if I need the list of identity values which get generated by INSERT statements?

One way is before insert I should get the MAX ID; after sucessfull insert again I get the ID and select the ID which are in betwwen them, like...

DECLARE @MinId INT 2: DECLARE @MaxId INT
 
SELECT @MinId = MAX(ID) from SearchResult
 
INSERT INTO SearchResult(Keyword, Hits)
SELECT Keyword, Hits FROM TmpTable
 
SELECT @MaxId = MAX(ID) from SearchResult
 
SELECT ID FROM SearchResult WHERE ID > @MinID AND <= @MaxID

But the result will not always true, like in the world of multitasking, what if some other insert also take place??

Now? How can we get all the newly added or created identities?

The other and best way is using OUTPUT CLAUSE. Here we go...

DECLARE @IDTable Table
{
    Id BIGINT
}
 
INSERT INTO SearchResult(Keyword, Hits)
OUTPUT INSERTED.ID INTO @IdTable

SELECT Keyword, Hits FROM TmpTable

The inserted IDs will be in magic table called INSERTED, and by using OUTPUT CLAUSE we can grab it and save it to temp table or temporary variable... from there we can select the newly added IDs, like..

SELECT ID FROM @IDTable

Read more on OUTPUT CLAUSE