Início / dba / Perguntas / 249751
Accepted
Ganesh_55
Asked: 2019-09-27 22:40:47 +0800 CST

mover dados antigos para outra tabela ao tentar inserir dados

  • 772

Tenho uma tabela com muitos dados. Quando tento inserir dados anteriores a 3 anos, gostaria que os dados fossem inseridos em outra tabela.

Minha mesa está assim.

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
sql-server

1 respostas

  1. Best Answer

    Como amostra:

    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" < 5;
    DELETE FROM main 
        FROM main 
        JOIN inserted ON main.id=inserted.id 
        WHERE inserted."date" < 5;
END;

    ou

    CREATE TRIGGER old_to_old 
ON main 
INSTEAD OF INSERT 
AS 
BEGIN
    INSERT INTO old (id, "date") 
        SELECT id, "date" FROM inserted WHERE "date" < 5;
    INSERT INTO main (id, "date") 
        SELECT id, "date" FROM inserted WHERE "date" >= 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;

    Resultado:

    identificação | data | de onde
-: | ---: | :-------------
 7 | 7 | a Principal      
10 | 10 | a Principal      
 4 | 4 | velho       
 1 | 1 | velho

    violino (PARA)

    violino (EM VEZ DE)

    • 0

relate perguntas