using OUTPUT in a update statement

Question

when I run an update in the production systems, I generally save the records into a backup table so that it is easy to recover if needs be.

I would like to get this done using the OUTPUT clause, can someone help with the syntax below?

SELECT @@SERVERNAME
go
use Product
go


BEGIN TRANSACTION T1

--this is what I would like to achieve using the OUTPUT CLAUSE:
--SELECT * 
--INTO tablebackups.dbo._MM_20140331_ItemDetail
--FROM  ItemDetail

-- now the update code:

SELECT @@TRANCOUNT AS [Active Transactions]
  ,@@SERVERNAME as [Server Name]
  ,DB_NAME() as [Database Name]

declare @CurrentUser nvarchar(128),
  @CurrentDate datetime

set @CurrentUser = suser_sname()
set @CurrentDate = getdate()

Update ItemDetail
Set   IsActive = 0,
  BuyingStatus = 'Deleted',
  ModifiedBy = @CurrentUser,
  ModifiedDate = @CurrentDate,
  ModifiedCount = ModifiedCount + 1

output deleted.* 
       into tablebackups.dbo._MM_20140331_ItemDetail 

FROM ItemDetail

Where ItemID in (
2319848,
2319868,
2319888,
2319908,
2319928,
2319938,
2319948,
2319958,
2319968,
2319988,
2320008,
2320028,
2320048,
2320068,
2320078,
2320088,
2320098,
2320108
) 


select @@trancount
--COMMIT
--ROLLBACK

Answer 1

I got it working now, the reasons why it was not working before are 2:

1) The OUTPUT does not create a new table. to work around this I used the following code:

select * 
into tablebackups.dbo._MM_20140331_ItemDetail_output
from ItemDetail
where 1 = 0 
--(0 row(s) affected)

2) The table I was updating had a timestamp field and it was giving me the following error:

--=========================================================
-- I had to specify the fields -- just because of the error below:

--Msg 273, Level 16, State 1, Line 40
--Cannot insert an explicit value into a timestamp column. 
--Use INSERT with a column list to exclude the timestamp column, 
--or insert a DEFAULT into the timestamp column.
--=========================================================

so I had to add the fields to my OUTPUT as follows:

declare @CurrentUser nvarchar(128),
@CurrentDate datetime

set @CurrentUser = suser_sname()
set @CurrentDate = getdate()

Update ItemDetail
Set   IsActive = 0,
BuyingStatus = 'Deleted',
ModifiedBy = @CurrentUser,
ModifiedDate = @CurrentDate,
ModifiedCount = ModifiedCount + 1


output deleted.[ItemID]
  ,deleted.[IsActive]
  ,deleted.[CreatedBy]
  ,deleted.[CreatedDate]
  ,deleted.[ModifiedBy]
  ,deleted.[ModifiedDate]
  ,deleted.[ModifiedCount]
  ,deleted.[BuyingStatus]  
into tablebackups.dbo._MM_20140331_ItemDetail_output
                                                   ([ItemID]
                                                   ,[IsActive]
                                                   ,[CreatedBy]
                                                   ,[CreatedDate]
                                                   ,[ModifiedBy]
                                                   ,[ModifiedDate]
                                                   ,[ModifiedCount]
                                                   ,[BuyingStatus])

FROM ItemDetail

Where ItemID in (
2319848,
2319868,
2319888,
2319908,
2319928,
2319938,
2319948,
2319958,
2319968,
2319988,
2320008,
2320028,
2320048,
2320068,
2320078,
2320088,
2320098,
2320108
) 

Now it all works fine.