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!

UPDATE
Archive
SET
@OldID = ID,
OldValue = OriginalTable.Value
SELECT
Value
FROM OriginalTable
WHERE
OriginalTable.AddDate < GETDATE() - 30

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.

UPDATE
Archive
SET
OldValue = VALUE
OUTPUT
inserted.OldID
INTO @UpdatedTable
SELECT
Value
WHERE
AddDate < GETDATE() - 30

Now all your OldIDs are going into @UpdatedTable and, naturally, you will not only be able to work with multiple rows this way, but also multiple columns, if you need to.

Here is a final example- a real-life sample of what I use when I need threading/queuing supported by the DB itself.
Take a look, see how it makes sure that the pull is done with no locking whatsoever? It’s quick and straight-forward this way. And all we do then is follow-up by wrapping it with an update of the table- it “locks” the @ELimit number of rows for a controlled period of time….and on top of that, gives back the primary column (IDs) back.
Note that you could just grab all of the needed data without any “fake” locks, we simply choose to do another locking-select right after to do a final recheck that *we* (the @User) did the lock (can never trust the damn engine, can we?).

UPDATE TOP (@ELimit)
[OriginalTable]
SET
LockDate = @LockAtDateBig,
LockUser = @User
--,RetryCount = ISNULL(RetryCount, 0) + 1
OUTPUT
inserted.ID
INTO @IDs
WHERE
ID IN (SELECT TOP (@ELimit)
ID
FROM
dbo.OriginalTable(NOLOCK)
WHERE
(
LockDate < DATEADD(minute, -10, @LockAtDateBig)
OR LockDate IS NULL
)--AND someothermainlogic, including possibly retrycounter limit
)
Image courtesy of Google Image search and an unknown person with an HP scanner, apparently- the search for the source dies there somewhere.