归档存储过程 [英] Archival stored procedures

问题描述

大家早上好，我想弄清楚如何将某些行从一张表移动到另一张表.这是踢球者；此活动表中的每一行的日期格式为字母 D 和 yyyymmdd(例如今天将是 D20161020).我想要做的是获取上个月的所有行(例如 D201609##)并将它们移动到存档表(复制然后删除复制的行).

Good morning everyone, I am trying to figure out how to move certain rows from one table to another. Here is the kicker; each row in this active table has a date format of the letter D with yyyymmdd (for example today would be D20161020). What I am trying to do is to take all rows from the previous month (for example D201609##) and move them to an archive table (copy then remove the copied rows ).

我的计划和想法是尽可能快地保持当前数据的精简.是的，我正在使用索引，我只是不希望表变得那么大.:) 每天，我将 3000 多条记录导入该表.为了精简，我通过记录 ID 引用主记录表.本质上，该表具有主记录表 RecID 的 RecID 外键和 Dyyyymmdd 格式的日期.我将该日期的计数放入相应的记录中.

My plan and thinking is to keep the current data lean to be as quick as possible. And yes I am using indexes, I just do not want the table to get that big. :) Each day, I import 3000+ records into that table. For slimness, I am referring by record ID to the master record table. Essentially, this table has a RecID Foreign Key to the master record table RecID and the date in that format Dyyyymmdd. I place the count for that date into the corresponding records.

这是另一件事，我希望只在活动表中保留当月的数据.

Here is the other thing, I am looking to only keep the current month's data in the active table.

我计划按年份保留存档表，即 2016counts 和 2017counts 将保存当年的数据.

I plan to keep the archive tables by year, i.e 2016counts, 2017counts which will hold that year's data.

对于年度存档表，我可以简单地创建一个过程，将当前存档表中的所有数据复制到年存档，然后截断该活动表.或者重命名表并重新创建一个像它一样的空白表，因为活动存档表中除了第一列是主记录表中的相关记录 ID 外，将没有任何内容.

I figure for the yearly archive table, I can simply create a procedure that will copy all the data from the current archive table to the year archive and then truncate that active table. Or rename the table and just re-create a blank table like it since the active archive table will have nothing in it except for the first column which is a correlated record ID from a master table of records.

推荐答案

这里有一个示例，其中包含执行您所要做的事情所需的大部分内容.希望这些评论对每一步都在做什么是不言自明的.下面的代码是可重新运行的，因此您可以查看它生成的输出并对其进行调整以测试不同的变化.

Here's a sample that contains most of what you'll need to do what you are after. Hopefully the comments are self explanatory as to what each step is doing. The below code is re-runnable, so you can view the output it generates and tweak it to test different variations.

CREATE TABLE #Main ( DateVal NVARCHAR(15) -- should be a date really!!!
                     )

CREATE TABLE #Archive ( DateVal NVARCHAR(15) -- should be a date really!!!
                        )

INSERT  INTO #Main
        ( DateVal
        )
        SELECT  'D20161001'
        UNION ALL
        SELECT  'D20161002'
        UNION ALL
        SELECT  'D20161003'
        UNION ALL
        SELECT  'D20161004'
        UNION ALL
        SELECT  'D20160913'
        UNION ALL
        SELECT  'D20160911'
        UNION ALL
        SELECT  'D20161007'
        UNION ALL
        SELECT  'D20160901'

-- values as they are
SELECT  DateVal
FROM    #Main

-- values with dates formatted from values and month extracted
SELECT  DateVal ,
        RIGHT(DateVal, 8) AS DateSection ,
        CAST(RIGHT(DateVal, 8) AS DATE) ConvertedToDate ,
        DATEPART(MONTH, ( CAST(RIGHT(DateVal, 8) AS DATE) )) MonthPart
FROM    #Main

-- values not in current month - to archive
SELECT  DateVal ,
        RIGHT(DateVal, 8) AS DateSection ,
        CAST(RIGHT(DateVal, 8) AS DATE) ConvertedToDate ,
        DATEPART(MONTH, ( CAST(RIGHT(DateVal, 8) AS DATE) )) MonthPart
FROM    #Main
WHERE   DATEPART(MONTH, ( CAST(RIGHT(DateVal, 8) AS DATE) )) < DATEPART(MONTH,
                                                              GETDATE())

-- insert the archived records
INSERT  INTO #Archive
        ( DateVal
        )
        SELECT  DateVal
        FROM    #Main
        WHERE   DATEPART(MONTH, ( CAST(RIGHT(DateVal, 8) AS DATE) )) < DATEPART(MONTH,
                                                              GETDATE())

-- delete the archived records
DELETE  #Main
WHERE   DATEPART(MONTH, ( CAST(RIGHT(DateVal, 8) AS DATE) )) < DATEPART(MONTH,
                                                              GETDATE())

-- see what's archived
SELECT  *
FROM    #Archive

-- see what's active
SELECT  *
FROM    #Main

DROP TABLE #Main
DROP TABLE #Archive

这篇关于归档存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！