CODEWITHSUNDEEP
sqlsql-server
Vinicius Cano
Vinicius Cano

Reputation: 319

Insert and delete in the same procedure?

I'm trying to delete the last row of a table,i have a procedure for that and it works,but before i delete the row,i need to move it to another table,to work similar as a backup,is there a way to do a insert and a delete in the same procedure or a need to create two different procedures?

Insert into T_CLIC_DELETE _LOG
values (iClic,iStatus ,dtDateCreated,iEDV)  
From T_STATUS_CLIC  
WHERE iClic = @iClic and dtDateCreated = (select max (dtDateCreated)  FROM T_STATUS_CLIC where iclic = @iClic );

and after a need to put the delete part:

DELETE  From T_STATUS_CLIC
WHERE iClic = @iClic and dtDateCreated = (select max (dtDateCreated)  
FROM T_STATUS_CLIC where iclic = @iClic );

Upvotes: 0

Views: 98

Answers (2)

Quassnoi
Quassnoi

Reputation: 425593

WITH    t AS
        (
        SELECT  TOP 1
                *
        FROM    t_status_clic
        WHERE   iClic = @iClic
        ORDER BY
                dtDateCreated DESC
        )
INSERT
INTO    t_clic_delete_log
SELECT  *
FROM    (
        DELETE
        FROM    t
        OUTPUT  DELETED.*
        ) q

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453563

Yes you can do it in the same procedure.

You can do it all in one statement too as long as T_CLIC_DELETE_LOG meets the requirements for tables that are target of the OUTPUT clause.

;WITH T
     AS (SELECT TOP 1 *
         FROM   T_STATUS_CLIC
         WHERE  iClic = @iClic
         ORDER  BY dtDateCreated DESC)
DELETE T
OUTPUT deleted.iClic,
       deleted.iStatus,
       deleted.dtDateCreated,
       deleted.iEDV
INTO T_CLIC_DELETE_LOG

(you can use TOP 1 WITH TIES if all rows should be deleted in the event of a tie for MAX(dtDateCreated))

Upvotes: 2

Related Questions