TSQL Output Tables

Using output tables when inserting multiple rows

If you need to do processing on individual rows when inserting multiple rows into a database table I'd normally use a cursor to achieve what I want.

Instead you can use the OUTPUT instruction to place the details into a temporary table...

DECLARE @TEMP TABLE (NEWID INT, OUTID INT)
INSERT INTO DESTTABLE (VAL1, VAL2, OLDVALUE)
OUTPUT INSERTED.ID, INSERTED.OLDVALUE INTO @TEMP
SELECT VAL1, VAL2, ID
FROM SRCTABLE

This can also be used for UPDATE and DELETE but it's important to remember that on INSERT commands there must be a column for the old value to be inserted