While loop on SELECT, DELETE ,INSERT statement-CodePudding

I have list of duplicate Transaction IDs and want to insert first the information to table Customer_Deleted_OrderRecordes as log then delete them from Orderstable.

This is my query:

DECLARE @Date Datetime
DECLARE @CustomerName nvarchar(90)
DECLARE @ItemID int 
DECLARE @TransactionID int
DECLARE @Invoice int
 
----selecting values------
SELECT 
    @Date = O.date,
    @CustomerName = c.FullName=,
    @ItemID = O.ItemID,
    @TransactionID = O.TransactionID,
    @Invoice = O.Invoice 
FROM
    CustomerTable c
INNER JOIN
    OrdersTable O ON c.ID = O.CustomerId
WHERE 
    O.TransactionID IN (1680339,1680340,1680341,1680342,1680343,1680344,1680345,1680346,1680347,1680348,1680349,1680350,1680351,1680352,1680353,1680354,1680355,1680356,1680357,1680358,1680359)

---This will insert values before deleting as log ---
INSERT INTO Customer_Deleted__OrderRecordes ([Date], [CustomerName], [ItemID], [TransactionID], [Invoice])
VALUES (@date, @CustomerName, @ItemID, @TransactionID, @Invoice)

---This will delete values after inserting

DELETE FROM OrdersTable 
WHERE TransactionID IN (1680339,1680340,1680341,1680342,1680343,1680344,1680345,1680346,1680347,1680348,1680349,1680350,1680351,1680352,1680353,1680354,1680355,1680356,1680357,1680358,1680359)

My query only effects one row instead of the all values selected in the select statement. How to write a while loop on SELECT, INSERT, DELETE statements?

CodePudding user response：

I don't get why you need to declare variables and use a loop, you can just insert with a select and then delete:

INSERT Customer_Deleted__OrderRecordes
  (Date, CustomerName, ItemID, TransactionID, Invoice)
SELECT O.date, c.FullName, O.ItemID, O.TransactionID, O.Invoice 
from CustomerTable c
inner join OrdersTable O on c.ID = O.CustomerId
WHERE O.TransactionID IN 
  (1680339, 1680340, 1680341, 1680342, 1680343, 1680344, 1680345,
   1680346, 1680347, 1680348, 1680349, 1680350, 1680351, 1680352,
   1680353, 1680354, 1680355, 1680356, 1680357, 1680358, 1680359)

And then just run the delete.

CodePudding user response：

One Delete trigger for your reference.

ALTER TRIGGER [dbo].[trgAfterDelete] 
     ON [dbo].[OrdersTable]
     AFTER DELETE
 AS BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON
     
     DECLARE @Date Datetime
     DECLARE @CustomerName nvarchar(90)
     DECLARE @ItemID int 
     DECLARE @TransactionID int
     DECLARE @Invoice int

     DECLARE cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
         SELECT 
             @Date = O.date,
             @CustomerName = c.FullName,
             @ItemID = O.ItemID,
             @TransactionID = O.TransactionID,
             @Invoice = O.Invoice 
         FROM
             CustomerTable c
         INNER JOIN
             DELETED O ON c.ID = O.CustomerId

     OPEN cur
     FETCH NEXT FROM cur INTO @Date, @CustomerName, @ItemID, @TransactionID, @Invoice
     WHILE @@FETCH_STATUS = 0
     BEGIN
         DECLARE 
               @BeforeChange XML
               
         SELECT @BeforeChange = (
             SELECT *
             FROM DELETED
             WHERE [TransactionID] = @TransactionID
             FOR XML RAW, ROOT
         )

         INSERT INTO Customer_Deleted_OrderRecordes ([Date], [CustomerName], [ItemID], [TransactionID], [Invoice])
             VALUES (@date, @CustomerName, @ItemID, @TransactionID, @Invoice)
         
         FETCH NEXT FROM cur INTO @Date, @CustomerName, @ItemID, @TransactionID, @Invoice
     END
     CLOSE cur
     DEALLOCATE cur
 END