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

user_321721 0
Viewed 115956

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?

sql-server sql
8 Answers

You can simply create procedure or job to overcome this issue like this:

 create procedure [dbo].[sp_LoadNewData]
 AS
INSERT INTO [dbo].[Table-A-History]
 (
 [1.Column Name], [2.Column Name], [3.Column Name], [4.Column Name]
 )    
 SELECT [1.Column Name], [2.Column Name], [3.Column Name], [4.Column Name]
 FROM dbo.[Table-A] S

 WHERE NOT EXISTS
 (
 SELECT  * FROM [dbo].[Table-A-History] D WHERE D.[1.Column Name] =S.[1.Column Name]
 )

Note: [1.Column Name] is common column for the tables.

user_120348300
Related