将行从Table1移动到Table2(存档)，然后将数据保留在表1中的7天

Question

table1具有包括创建日期的列。我想归档比过去7天大的排行行，我想将它们移至新桌子上，table2。如何将存档数据更新到表2中，仅在表1中仅保留数据。谢谢！

Answer 1

我会倾向于做类似的事情：

declare @cutoff_datetime datetime;
set @cutoff_datetime = dateadd(day, -7, getdate());
insert into table2 ( . . . )
    select . . .
    from table1
    where createdate < @cutoff_datetime;
delete from table1
    where createdate < @cutoff_datetime;

这在以下两种情况下是安全的：

• table1中未更新行；而且，
• 当前时间插入新行。

如果可以更新行，则应询问另一个问题，并具有更详细的规格。

Answer 2

- 创建一个临时表

CREATE TABLE #temp (PK_Col INT);  -- Only to store the Primary Key values of deleted rows.
GO

- 将行插入存档表

INSERT INTO Archive.Table (PK_Col , created_date , OtherCols,.....)
OUTPUT inserted.PK_Col INTO #Temp
SELECT PK_Col , created_date , OtherCols,.....
FROM Table1
WHERE created_date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-7), 0)
GO

- 现在删除存档数据

DELETE FROM t
FROM Table1 t
WHERE EXISTS ( SELECT 1 from #temp t1 WHERE t1.PK_Col = t.PK_Col)
GO

Answer 3

这使用 datadd()和 datediff()在7天前的一天开始，并在7天前提前从 table1删除行；使用 output 将已删除的行插入table2。

insert into table2 (col1, col2, some_date)
select d.col1, d.col2, d.some_date
from (
  delete 
  from table1
  output deleted.col1, deleted.col2, deleted.some_date
  where some_date <  dateadd(day, datediff(day, 0, getdate() -7), 0)
  ) d (col1, col2, some_date)

---

rextester演示：http：//rextester.com/dnk69844

create table table1 (col1 int not null identity(2,1), col2 varchar(32), some_date date);
insert into table1 values ('Eight','20170429'),('Seven','20170430'),('Six','20170501');
create table table2 (col1 int not null, col2 varchar(32), some_date date);
insert into table2 values (1,'Nine','20170428');
select tbl='table1', col1, col2, some_date=convert(char(10),some_date,120) from table1
union all
select tbl='table2', col1, col2, some_date=convert(char(10),some_date,120) from table2;
insert into table2 (col1, col2, some_date)
select d.col1, d.col2, d.some_date
from (
  delete 
  from table1
  output deleted.col1, deleted.col2, deleted.some_date
  where some_date <  dateadd(day, datediff(day, 0, getdate() -7), 0)
  ) d (col1, col2, some_date);

select tbl='table1', col1, col2, some_date=convert(char(10),some_date,120) from table1
union all
select tbl='table2', col1, col2, some_date=convert(char(10),some_date,120) from table2;

返回(之前(：

+--------+------+-------+------------+
|  tbl   | col1 | col2  | some_date  |
+--------+------+-------+------------+
| table1 |    2 | Eight | 2017-04-29 |
| table1 |    3 | Seven | 2017-04-30 |
| table1 |    4 | Six   | 2017-05-01 |
| table2 |    1 | Nine  | 2017-04-28 |
+--------+------+-------+------------+

返回(之后(：

+--------+------+-------+------------+
|  tbl   | col1 | col2  | some_date  |
+--------+------+-------+------------+
| table1 |    3 | Seven | 2017-04-30 |
| table1 |    4 | Six   | 2017-05-01 |
| table2 |    1 | Nine  | 2017-04-28 |
| table2 |    2 | Eight | 2017-04-29 |
+--------+------+-------+------------+