>I wonder if it is possible to insert a new row and select the result in the same command?
>I have a table that keeps track of incrementing ID numbers (auto inc) and like to get a new ID at the same time.
>I don't want to create a SP because it would be easier to maintain if I can keep it in the same statement, but like to run one statement only.
>
>This does not work:
>Select max(emID) From EmpId Where (Insert into empID (emKey) values (NewID()))
>
>It gives "Incorrect syntax near the keyword 'Insert'"
INSERT INTO myTable (Col1, Col2)
OUTPUT Inserted.Id, Inserted.Col1
VALUES (@Val1, @Val2)
If it's not broken, fix it until it is.
My Blog