将表1中的行移动到表2(存档),并将表1中的数据保留过去7天
问题描述:
表1具有包括创建日期的列。我想归档比过去7天更早的行,我希望将这些行移动到新的表格Table2中。 如何编写将更新归档数据到表2中的脚本,并且仅保留表1中过去7天的数据。谢谢!将表1中的行移动到表2(存档),并将表1中的数据保留过去7天
答
我会倾向于做这样的事情:
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
如果行可以被更新,你应该问另一个问题有更详细的规格你想要做什么。
答
这使用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 |
+--------+------+-------+------------+
答
- 创建临时表
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
您的代码?你困在哪里? –
INSERT INTO Table2(columns .....)从Table2中选择(列...)其中createddate> = DATEADD(day,-7,GETDATE())从Table1中删除where where createddate> = DATEADD(day,-7, GETDATE()) – muddu83