How to store historical records in a history table in SQL Server

I have 2 tables, Table-A and Table-A-History.

• Table-A contains current data rows.
• Table-A-History contains historical data

I would like to have the most current row of my data in Table-A, and Table-A-History containing historical rows.

I can think of 2 ways to accomplish this:

1. whenever a new data row is available, move the current row from Table-A to Table-A-History and update the Table-A row with the latest data (via insert into select or select into table)

   or

2. whenever a new data row is available, update Table-A‘s row and insert a new row into Table-A-History.

In regards to performance is method 1 or 2 better? Is there a better different way to accomplish this?

Answer

Logging changes is something I’ve generally done using triggers on a base table to record changes in a log table. The log table has additional columns to record the database user, action and date/time.

create trigger Table-A_LogDelete on dbo.Table-A
  for delete
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(), 'delete-deleted', @Now, *
      from deleted
go
exec sp_settriggerorder @triggername = 'Table-A_LogDelete', @order = 'last', @stmttype = 'delete'
go
create trigger Table-A_LogInsert on dbo.Table-A
  for insert
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(), 'insert-inserted', @Now, *
      from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogInsert', @order = 'last', @stmttype = 'insert'
go
create trigger Table-A_LogUpdate on dbo.Table-A
  for update
as
  declare @Now as DateTime = GetDate()
  set nocount on
  insert into Table-A-History
    select SUser_SName(), 'update-deleted', @Now, *
      from deleted
  insert into Table-A-History
    select SUser_SName(), 'update-inserted', @Now, *
      from inserted
go
exec sp_settriggerorder @triggername = 'Table-A_LogUpdate', @order = 'last', @stmttype = 'update'

Logging triggers should always be set to fire last. Otherwise, a subsequent trigger may rollback the original transaction, but the log table will have already been updated. This is a confusing state of affairs.