INSERT INTO SELECT from UPDATE

I need to select some values from a table to be updated, and then update them right away. Furthermore, I need to insert one new record in a table for each updated record. To select records and update I am using a structure like

UPDATE TableA SET SomeFie开发者_如何学JAVAld = 1 OUTPUT RecordID FROM TableA WHERE RecordID IN
(    
    SELECT TOP @Something RecordID FROM TableA    
)

Now, for the insert part, I would like to wrap the UPDATE statement into an INSERT INTO SELECT, thus taking advantage of the OUTPUT clause. However, SQL complains when I do

INSERT INTO TableA SELECT ( RecordID , GETDATE() ) FROM
(
    UPDATE TableA SET SomeField = 1 OUTPUT RecordID FROM TableA WHERE RecordID IN
    (        
        SELECT TOP @Something RecordID FROM TableA        
    )
)

Can't I do it all in one statement, even with the OUTPUT clause?

UPDATE TableA SET SomeField = 1 
OUTPUT inserted.RecordID, GETDATE() into TableA (RecordID , DT)
FROM TableA 
WHERE RecordID IN
(        
    SELECT TOP @Something RecordID FROM TableA        
)

Just not sure - you're trying to insert updated rows again ?

It is posible to use output to insert the updated rows from one table to another: However I cannot make that syntax you are using to work. Please check out this link

Of course, you could try something like this:

INSERT INTO TableA (RecordID, Value)
SELECT RecordID, GETDATE()
FROM OPENQUERY(
  yourserver,
  'UPDATE TableA
  SET SomeField = 1
  OUTPUT inserted.RecordID
  WHERE RecordID IN (SELECT TOP (5) RecordID FROM TableA)'
)

But there's a couple of issues with the approach:

1. You'd need to create a linked server yourserver.

2. The 'remote' query wouldn't be very swift.

3. You'd have hard time replacing TOP (5) with TOP (@Something). Actually you'd most probably have to turn the entire statement into a dynamic query. (That's right, you'd have to put the already dynamic UPDATE inside another dynamic query.)

I expect, with the last issue the one-statement limitation would finally be broken.

So, instead, why not have it like this:

DECLARE @tmpRecords TABLE (RecordID int);

UPDATE TableA
SET SomeField = 1
OUTPUT inserted.RecordID INTO @tmpRecords (RecordID)
WHERE RecordID IN (SELECT TOP (@Something) RecordID FROM TableA);

INSERT INTO TableA (RecordID, SomeDateColumn)
SELECT RecordID, GETDATE()
FROM @tmpRecords;