CODEWITHSUNDEEP
sql-servert-sqlsql-insertproceduredatabase-trigger
John Doe
John Doe

Reputation: 71

How to generate an automatic INSERT script to a table

I have just started with SQL Server, I don't know if it is with a job, trigger or a procedure, in short, what I need that you can support me is in doing the following: in a database where I store the record of some requirements, which are associated to a state where they can be Completed or Closed, the requirements that are in the Completed state after one week (exactly 7 days) must automatically change status to Closed, but in addition to that I need you to perform the INSERT (automatic) of that record that was made, means that all the data in the row are inserted the same and the only thing that changes is the column that corresponds to state, which in this case would be finished.

The following is the query with which I obtain the records of the requirements longer than 7 days with the status 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()));

Until there I don't know how to perform the automatic INSERT into that table.

TK_CT_STATUS_ID corresponds to the TMN Status Identifier for Completed, CDO for Closed.

UPDATE:

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 ..................;

Upvotes: 1

Views: 152

Answers (2)

GMB
GMB

Reputation: 222722

You have done the hardest part of the work by generating a select query that returns the records that need to be updated. A simple way to turn it into an update statement is to leverage the concept of updatable common table expression, that SQL Server supports:

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'

Edit

If you are looking for an insert statement instead, then you can use the INSERT ... SELECT ... syntax, like:

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

Please note that this will only work if TK_CT_STATUS_ID is part of all unique keys of your table (including the primary key); otherwise you will get key constraint error when trying to insert new records.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

You don't need a cte for this. It is just an update statement. I changed up your query a bit to utilize aliases. When everything is in all upper case it is so difficult to read.

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

Upvotes: 1

Related Questions