Add support for SQL Server DML OUTPUT clauses

[lukaseder]

In T-SQL, there is an OUTPUT clause that can be used with all DML statements in order to access values prior to the execution of the DML manipulation:

For example:

DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;

This issue is best coordinated with #3185. As the SQL Server OUTPUT clause is issued in the middle of a statement, it may be a bit difficult to keep track of the output <R> type. Specifically, since these DSL DML statements are already generic, we cannot add another parameter backwards-compatibly.

An option would be not to implement OUTPUT, but to follow PostgreSQL syntax (see also #3441)

---

See:

• http://technet.microsoft.com/en-us/library/ms177564.aspx
• http://www.jooq.org/doc/latest/manual/sql-building/sql-statements/merge-statement/#comment-1488566189 (comment by Steve Ash)

[lukaseder]

This needs some more thought. So far, we haven't found an easy way to implement this in our API. Putting it at the beginning of statements (as it is in SQL Server) will:

• Either require dragging the result type through all of the statement, such as DELETE
• Or it will mean that we won't know the result type

Another option would be to put it at the end along with the RETURNING clause, but that might prove to be confusing

[lukaseder]

jOOQ has supported OUTPUT via emulations of RETURNING for a long time now. While there are subtle differences (e.g. whether trigger-generated values are included or not), as well as the fact that OUTPUT can access both the pre and post DML values in the case of an UPDATE, it's not worth adding this additional overhead to the API and emulating it everywhere.