SQL - The Power of OUTPUT

Insert..Select is a great command, but sometimes you just need more- sometimes you need to return bits of the information back.

The first thing you will attempt, of course, is to declare the few variables you need. Yes, you will see that you can indeed do that, along with updating columns, all in one call- neat!

[syntax_prettify linenums=”linenums”]
UPDATE
Archive
SET
@OldID = ID,
OldValue = OriginalTable.Value
SELECT
Value
FROM OriginalTable
WHERE
OriginalTable.AddDate < GETDATE() - 30
[/syntax_prettify]

Unfortunately not only other types of statements (i.e. delete and insert) will fail to do that, but your variable(s) will hold only one value. Yes, you could concatenate it…but are you really going to be doing this crazy 20th century workaround nonsense again? Our old OUTPUT keyword to the rescue- it redirects the processed information back to where you tell it, including a variable table that you might have created just for that purpose.