CREATE TABLE actualData (ID INT NOT NULL, Col1 VARCHAR(10), Col2 VARCHAR(10))

CREATE TABLE historicalData (ID INT NOT NULL, Date DATETIME DEFAULT GETDATE(), Col1 VARCHAR(10), Col2 VARCHAR(10))

CREATE TRIGGER trg_actualData ON actualData FOR UPDATE

AS

INSERT INTO historicalData (ID, Col1, Col2) SELECT Id, Col1, Col2 FROM deleted

INSERT INTO actualData VALUES (1, 'test', '1rst')

UPDATE actualData SET Col2 = '2nd' WHERE ID = 1

UPDATE actualData SET Col2 = '3rd' WHERE ID = 1

SELECT * FROM actualData

SELECT * FROM historicalData

In this example you will insert every change on actualData in historicalData.

If you only want to store the last data from actualData then you have to change the trigger.

CREATE TABLE actualData1 (ID INT NOT NULL, Col1 VARCHAR(10), Col2 VARCHAR(10))

CREATE TABLE historicalData1 (ID INT NOT NULL, Date DATETIME DEFAULT GETDATE(), Col1 VARCHAR(10), Col2 VARCHAR(10))

CREATE TRIGGER trg_actualData1 ON actualData1 FOR UPDATE

AS

MERGE historicalData1 AS trg

USING (SELECT ID, Col1, Col2 FROM deleted) AS src (ID, Col1, Col2) ON src.ID = trg.ID

WHEN MATCHED THEN

UPDATE SET trg.Col1 = src.Col1, trg.Col2 = src.Col2

WHEN NOT MATCHED BY TARGET THEN

INSERT (Id, Col1, Col2) VALUES (ID, Col1, Col2);

INSERT INTO actualData1 VALUES (1, 'test', '1rst')

UPDATE actualData1 SET Col2 = '2nd' WHERE ID = 1

UPDATE actualData1 SET Col2 = '3rd' WHERE ID = 1

SELECT * FROM actualData1

SELECT * FROM historicalData1