Would like to understand a better way of updating data that a cursor if possible

Question

Would like to understand a better way of updating data that a cursor if possible

Ten Centuries

Points: 1117

August 15, 2022 at 5:46 pm

I have a need to update some data and also create a record of that change. From what I know and I don't know a whole hell of a lot, I can do an update with update table set field value where value = value but to make a record of the change I need the cursor correct?

The Insertm2mhistory inserts the actual record in the history table. but I don't need that if there is a better way.

Declare @partno char(35)
Declare @rev char(3)
Declare @id numeric(10)
Declare @now datetime
set @now = current_timestamp

Declare upcsr cursor for Select inmast_ext.identity_column fpartno, frev from inmast_ext
inner join inmastx on inmastx.identity_column = fkey_id where txtschedb = '8479909440'

Open updcsr
Fetch next from updcsr into @part, @rev, @id

While @@FETCH_STATUS = 0
Begin
update inmast_ext set TXTSCHEDB = '8479909640' where identity_column = @id


Exec m2maux01..SP_InsertM2MHistory 'E', 'TXTSCEHDB', 'INV', 'INMAST_EXT', 'BWHIPP', @now, @partno, @partno, @rev, '8479909640', '8479909640', '03'


Fetch next from updcsr into @part, @rev, @id 
End

Close updcsr
deallocate updcsr

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

A far better method would be to write a trigger to do the copy to the history table. Make sure you write it to handle more than one row at a time.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

p.s. If the table isn't too wide, consider the use of a "System Versioned Temporal Table" which is all of that done auto-magically for you.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bswhipp Ten Centuries Points: 1117 More actions · Answer 3

bswhipp

Ten Centuries

Points: 1117

August 15, 2022 at 6:37 pm

#4076663

Ah, never thought of a trigger. That would work.