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 )