Output Clause in MSSQL 2005

In MSSQL when you insert or delete a record, you don`t get a returned recordset. It is always a headache to first determine what is the record that was deleted or inserted when there is a auto increment index present.

Starting fom MSSQL 2005 there is an interesting command which resembles MYSQL mysql_insert_id() function. It has more functionality than MYSQL. It can be used in INSERT, DELETE, UPDATE clause to get the id involved.

For example

insert into tbl_test_insert (case_id) output INSERTED.ID, INSERTED.case_id values(`123456789`)

The query is the same as any INSERT query except there is some extra clause inserted in between. The clause is OUTPUT INSERTED.ID, INSERTED.case_id. This clause tells the query to output values defined by INSERTED. The value is actually the record or the data that was inserted/deleted into the table.

It comes very handy if you have to insert/delete a main table and then insert into a sub table that has the same id as the main record. The id in the main record is auto increment identity column so that it could be an index. You will not know what is the value till the record is inserted. By inserting/deleting a record in the main table you will need to get the id value so that you could perform insert/delete on the sub table.

I used to insert a UUID when inserting the main table then query the main table to get the id by finding the UUID. Now I can just use the insert statement then simply do a odbc_fetch_row() to get the id with just one query. Neat.

Just to let you know this- use DELETED.xx if you are doing delete query. For UPDATE you use INSERTED for those fields you updated and use DELETED for those fields you replaced.