如何为表生成自动INSERT脚本

2024-05-15 • 问答

我刚开始使用SQL Server，我不知道它是与作业，触发器还是过程一起使用的，简而言之，我需要您支持我的是进行以下操作：在数据库中存储某些要求的记录，这些记录与可以是Completed或Closed的状态相关联，一周后（恰好7天）必须处于Completed状态的要求会自动将状态更改为Closed，但除此之外，我还需要您执行已创建记录的INSERT（自动），这意味着该行中的所有数据都插入相同，并且唯一改变的是与状态相对应的列，在这种情况下将完成。

以下是查询，该查询用于获取状态为Completed且超过7天的需求记录。

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对应于Completed的TMN状态标识符，对应于Closed的CDO。

更新：

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,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,GETDATE()))

请注意，仅当TK_CT_STATUS_ID是表的所有唯一键（包括主键）的一部分时，此方法才有效；否则，当您尝试插入新记录时，您将得到关键约束错误。

您不需要cte。这只是一个更新语句。我稍微修改了您的查询以利用别名。当所有内容全部大写时，很难阅读。

update r
set TK_CT_STATUS_ID = 'CDO'
FROM TK_HD_TICKETS AS t
INNER JOIN TK_DT_RECORDS r ON t.TK_HD_TICKETS_ID = r.TK_HD_TICKETS_ID
WHERE r.TK_DT_RECORDS_ID = (
                                SELECT MAX(r2.TK_DT_RECORDS_ID) 
                                FROM TK_DT_RECORDS r2
                                WHERE t.TK_HD_TICKETS_ID = r2.TK_HD_TICKETS_ID
                            )
AND r.TK_CT_STATUS_ID = 'TMN'
AND r.ACTIVITY_DATE < DATEADD(DAY,GETDATE());

如何为表生成自动INSERT脚本

cwq1994 回答：如何为表生成自动INSERT脚本

大家都在问