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...
But the result will not always true, like in the world of multitasking, what if some other insert also take place??
DECLARE @MinId INT 2: DECLARE @MaxId INTSELECT @MinId = MAX(ID) from SearchResultINSERT INTO SearchResult(Keyword, Hits)
SELECT Keyword, Hits FROM TmpTableSELECT @MaxId = MAX(ID) from SearchResultSELECT ID FROM SearchResult WHERE ID > @MinID AND <= @MaxID
Now? How can we get all the newly added or created identities?
The other and best way is using OUTPUT CLAUSE. Here we go...
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..
DECLARE @IDTable Table
}INSERT INTO SearchResult(Keyword, Hits)
OUTPUT INSERTED.ID INTO @IdTable
SELECT Keyword, Hits FROM TmpTable
SELECT ID FROM @IDTable
Read more on OUTPUT CLAUSE