How to update a history table with changes.

Question

How to update a history table with changes.

Ten Centuries

Points: 1125

April 2, 2025 at 11:53 am

Good morning to all. I am a novice when it comes to SQL so my question is TSQL related on how to update a history table with changes to thousands of records without a cursor. Currently here is my code with cursor. I know I can update the price for all the parts in one statement but how do I update the history table (if possible without a cursor)? Thanks

Declare @id int
Declare @part char(35)
Declare @rev char(3)
Declare @price money
Declare @newprice money
Declare @newVal char(50)
Declare @oldval char(50)

--Backup
Select * into inmastx_before_PriceUpdate_4_2_2025 from inmastx

--Need cursors to update history table
Declare Csr cursor for select identity_column, fprice, fpartno, frev from inmastx where 
(fprodcl = 'XC' or fprodcl = 'QS') and fprice > 0
Open csr

Fetch Next from csr into @id, @Price, @part, @rev
While @@FETCH_STATUS = 0
Begin
set @newprice = Ceiling(@price*1.03)
Update inmastx set fprice = @newprice where identity_column = @id

insert into m2mdata01.dbo.syecaudt (fcdatabase, fcconnection, fcaction, fcfield, fcform, fctable, fcuser, 
ftdate, fcmaster, fcprimekey, fmnewval, fmoldval, fcActFrom) values('01', '', 'E', 'FPRICE', 'INV', 'INMAST', 'BWhipp '
, CURRENT_TIMESTAMP, rtrim(@part), rtrim(@Part) + '|'+rtrim(@rev)+'|Default', 
convert(varchar(15), @newprice), convert(varchar(15), @price), '')

Fetch Next from csr into @id, @price, @part, @rev
End

Close csr
Deallocate csr

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Once you have created your set-based update code, consider adding an OUTPUT clause to it. This will allow you to capture the before and after state of the rows which were updated. You should be able to satisfy your logging requirements using this method.

Have a look here for some examples:

https://www.codelabs365.com/sql-cookbook/sql-server/tsql-capture-changes-with-update-and-output-into-regular-table-or-temp-table-or-table-variable/

bswhipp Ten Centuries Points: 1125 More actions · Answer 2

Phil, thanks for the quick reply. I am getting an error on the table. Apparently it can not be a common table expression? So I would have to do it to a temp table then import the temp table to the regular table?