move old data to another table when try to insert data I have a table with lots of data. When I try to insert data from before 3 years ago, I would like the data to be inserted into another table instead.

My table looks like this.

Id      SecurityID  Date        Open    High    Low     Close   Volume  OpenInit
462151  998         2016-08-16  37740.0 37740.0 37570.0 37630.0 0.00000 0.00000
462152  998         2016-08-17  37780.0 37830.0 37710.0 37760.0 0.00000 0.00000
462153  998         2016-08-18  37600.0 37800.0 37600.0 37760.0 0.00000 0.00000
462478  999         2017-01-24  46970.0 47480.0 46660.0 47310.0 0.00000 0.00000
462479  999         2017-01-25  47480.0 48630.0 47440.0 48380.0 0.00000 0.00000
462480  999         2017-01-26  48100.0 48690.0 47940.0 48310.0 0.00000 0.00000
462789  1000        2017-06-02  45680.0 45800.0 45500.0 45640.0 0.00000 0.00000
462790  1000        2017-06-05  45200.0 45650.0 45090.0 45420.0 0.00000 0.00000
462791  1000        2017-06-06  45390.0 45530.0 45120.0 45350.0 0.00000 0.00000
As a sample:

CREATE TABLE main (id INT, "date" INT);
CREATE TABLE old (id INT, "date" INT);


CREATE TRIGGER old_to_old 
ON main 
FOR INSERT 
AS 
BEGIN
    INSERT INTO old (id, "date") 
        SELECT id, "date" FROM inserted WHERE "date" &lt; 5;
    DELETE FROM main 
        FROM main 
        JOIN inserted ON main.id=inserted.id 
        WHERE inserted."date" &lt; 5;
END;


or

CREATE TRIGGER old_to_old 
ON main 
INSTEAD OF INSERT 
AS 
BEGIN
    INSERT INTO old (id, "date") 
        SELECT id, "date" FROM inserted WHERE "date" &lt; 5;
    INSERT INTO main (id, "date") 
        SELECT id, "date" FROM inserted WHERE "date" &gt;= 5;
END;


INSERT INTO main VALUES (1,1), (4,4), (7,7), (10,10);

SELECT *, 'main' from_where FROM main
UNION ALL
SELECT *, 'old' FROM old;


Output:



id | date | from_where
-: | ---: | :---------
 7 |    7 | main      
10 |   10 | main      
 4 |    4 | old       
 1 |    1 | old       



fiddle (FOR)

fiddle (INSTEAD OF)