In SQL Server is it possible to get "id" of a record when Insert is executed?
In .Net at least, you can send multiple queries to the server in one go. I do this in my app:
command.CommandText = "INSERT INTO [Employee] (Name) VALUES (@Name); SELECT SCOPE_IDENTITY()";int id = (int)command.ExecuteScalar();Works like a charm.
If you're inserting multiple rows, the use of the OUTPUT and INSERTED.columnname clause on the insert statement is a simple way of getting all the ids into a temp table.
DECLARE @MyTableVar table( ID int, Name varchar(50), ModifiedDate datetime); INSERT MyTable OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar SELECT someName, GetDate() from SomeTable