Archiving data while running update statement

Question

I'm using sql server and I'm trying to update certain columns in a table but before it updates the data, I want to insert the data into another table.

This is my code

declare @table1 table (Id int, Name varchar(100))
declare @temp table (Id int, Name varchar(100), CreatedOn datetime default getdate())
insert into @table1 values (1, 'Albert')
insert into @table1 values (2, 'Alex')
insert into @table1 values (3, 'Alas')
insert into @table1 values (4, 'Bob')
update @table1
set Name = 'B' + Name
output Deleted.Id, Deleted.Name, getdate() into @temp
where Name like 'A%'
select * from @table1
select * from @temp

Above code seems to be working correctly. Please let me know if I have to change anything.

• DForck42: Is this happening on a set schedule, or every time the data is being updated?
• Harsha: Not actually a schedule nor every time when data is updated... its a custom script. its executed when something bulk has to be deactivated.

Answer 1

You don't need to change anything as long as the code performs what you need. If you want to insert records to another table on every update (and not only in this place) you should probably take a look at triggers

• Harsha: actually I need this data only for this script... I so might not be going for triggers. thank you for the suggestion.