如何为表生成自动INSERT脚本

约翰·杜

我刚开始使用SQL Server,我不知道它是与作业,触发器还是过程一起使用的,总之,我需要您支持我的工作是执行以下操作:在存储记录的数据库中某些需求(可能与其处于某个状态Completed状态相关)中,一个星期(恰好是7天)后Closed处于该Completed状态的需求必须自动将状态更改为Closed,但除此之外,我还需要您执行INSERT(自动)代表已完成的记录,意味着该行中的所有数据都以相同的方式插入,唯一改变的是与状态相对应的列,这种情况在本例中将完成。

以下是查询,我可以通过该查询获取状态为7天以上的需求记录Completed

SELECT TK_DT_RECORDS.*
FROM TK_HD_TICKETS AS TICKETS
INNER JOIN TK_DT_RECORDS ON TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID
WHERE TK_DT_RECORDS.TK_DT_RECORDS_ID = (SELECT MAX (TK_DT_RECORDS_ID) 
                                        FROM TK_DT_RECORDS 
                                        WHERE TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID)
  AND (TK_DT_RECORDS.TK_CT_STATUS_ID = 'TMN') 
  AND (TK_DT_RECORDS.ACTIVITY_DATE < DATEADD(DAY, 7, GETDATE()));

直到那里,我都不知道如何在INSERT该表中执行自动操作

TK_CT_STATUS_ID对应于的TMN状态标识符Completed,的CDO Closed

更新:

WITH CTE AS 
(
    SELECT TK_DT_RECORDS.*
    FROM TK_HD_TICKETS AS TICKETS
    INNER JOIN TK_DT_RECORDS ON TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID
    WHERE TK_DT_RECORDS.TK_DT_RECORDS_ID = (SELECT MAX (TK_DT_RECORDS_ID) 
                                            FROM TK_DT_RECORDS 
                                            WHERE TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID)
      AND (TK_DT_RECORDS.TK_CT_STATUS_ID = 'TMN') 
      AND (TK_DT_RECORDS.ACTIVITY_DATE < DATEADD(DAY, 7, GETDATE()))
)
INSERT INTO TK_DT_RECORDS ([TK_DT_RECORDS_ID], [ACTIVITY_DATE], [CONTENT],[TK_HD_TICKETS_ID], [NOTE], [USER_UPDATE], [TK_CT_STATUS_ID], [TK_BT_EMPLOYEES_ID],[TK_CT_SERVICES_ID], [TK_CT_PRIORITIES_ID], [TK_CT_CATEGORIES_ID], TK_CT_SUBSERVICES_ID]) 
VALUES ..................;
专线小巴

通过生成一个select查询,查询返回需要更新的记录,您已经完成了最艰巨的工作将其转换为update语句的一种简单方法是利用SQL Server支持的可更新公用表表达式的概念:

WITH CTE AS (
    SELECT TK_DT_RECORDS.*
    FROM TK_HD_TICKETS AS TICKETS
    INNER JOIN TK_DT_RECORDS ON TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID
    WHERE TK_DT_RECORDS.TK_DT_RECORDS_ID = (SELECT MAX (TK_DT_RECORDS_ID) FROM TK_DT_RECORDS 
    WHERE TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID )
    AND (TK_DT_RECORDS.TK_CT_STATUS_ID = 'TMN') 
    AND (TK_DT_RECORDS.ACTIVITY_DATE < DATEADD(DAY, 7, GETDATE()))
)
UPDATE CTE SET TK_CT_STATUS_ID = 'CDO'

编辑

如果要查找insert语句,则可以使用以下INSERT ... SELECT ...语法:

INSERT INTO TK_DT_RECORDS(
    [TK_DT_RECORDS_ID],
    [ACTIVITY_DATE],
    [CONTENT],
    [TK_HD_TICKETS_ID],
    [NOTE],
    [USER_UPDATE],
    [TK_CT_STATUS_ID],
    [TK_BT_EMPLOYEES_ID],
    [TK_CT_SERVICES_ID],
    [TK_CT_PRIORITIES_ID],
    [TK_CT_CATEGORIES_ID],
    [TK_CT_SUBSERVICES_ID]
)
SELECT 
    TK_DT_RECORDS.[TK_DT_RECORDS_ID],
    TK_DT_RECORDS.[ACTIVITY_DATE],
    TK_DT_RECORDS.[CONTENT],
    TK_DT_RECORDS.[TK_HD_TICKETS_ID],
    TK_DT_RECORDS.[NOTE],
    TK_DT_RECORDS.[USER_UPDATE],
    'CDO',
    TK_DT_RECORDS.[TK_BT_EMPLOYEES_ID],
    TK_DT_RECORDS.[TK_CT_SERVICES_ID],
    TK_DT_RECORDS.[TK_CT_PRIORITIES_ID],
    TK_DT_RECORDS.[TK_CT_CATEGORIES_ID],
    TK_DT_RECORDS.[TK_CT_SUBSERVICES_ID]
FROM TK_HD_TICKETS AS TICKETS
INNER JOIN TK_DT_RECORDS 
    ON TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID
WHERE 
    TK_DT_RECORDS.TK_DT_RECORDS_ID = (
        SELECT MAX (TK_DT_RECORDS_ID) 
        FROM TK_DT_RECORDS 
        WHERE TICKETS.TK_HD_TICKETS_ID = TK_DT_RECORDS.TK_HD_TICKETS_ID 
    )
    AND (TK_DT_RECORDS.TK_CT_STATUS_ID = 'TMN') 
    AND (TK_DT_RECORDS.ACTIVITY_DATE < DATEADD(DAY, 7, GETDATE()))

请注意,这仅TK_CT_STATUS_ID在表的所有唯一键(包括主键)的一部分中有效;否则,当您尝试插入新记录时,您将得到关键约束错误。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章