存储过程将数据从特定时间戳插入临时表并截断主表

I have a set of tables (_del tables) that keep track of all the deleted data. I want to copy the records with a timestamp of today and later from these tables into temporary tables. After that I want to truncate the _del table. Can someone help me with a stored procedure for the above logic.

我有一组表(_del表)跟踪所有已删除的数据。我想将带有今天和稍后时间戳的记录从这些表复制到临时表中。之后我想截断_del表。有人可以帮助我使用上述逻辑的存储过程。

1 个解决方案

#1

What database are you using? To copy from a single table into another table where a particular column's date is greater than now you could use this:

你用的是什么数据库?要从单个表复制到另一个表,其中特定列的日期大于现在,您可以使用:

SQL Server:

INSERT INTO MyTempTable1 (Column1, Column2, Colu...)
    SELECT Column1, Column2, Colu...
      FROM _delTable1
     WHERE MyDateColumn > GetDate();

TRUNCATE TABLE _delTable1;

MySQL:

INSERT INTO MyTempTable1 (Column1, Column2, Colu...)
    SELECT Column1, Column2, Colu...
      FROM _delTable1
     WHERE MyDateColumn > CURRENT_TIMESTAMP();

TRUNCATE TABLE _delTable1;

If you want to dynamically build the queries for each table that begins with _del then you could loop through the tables and build queries up and execute them - but it depends on what database you are using and even what version.

如果要为以_del开头的每个表动态构建查询,那么您可以循环遍历表并构建查询并执行它们 - 但这取决于您使用的数据库甚至是什么版本。

Another option (depending on what you want to do) is to just delete the data older than today (now or the start of today).

另一种选择(取决于你想要做什么)只是删除比今天更早的数据(现在或今天的开始)。

To get the start of today you could do this:

要开始今天你可以这样做:

SELECT DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), -1)

If you want to use that just replace the GetDate() or CURRENT_TIMESTAMP() with it (in brackets).

如果你想使用它只需用它替换GetDate()或CURRENT_TIMESTAMP()(在括号中)。

#1