SQL Server : while loop on SELECT, DELETE ,INSERT statement-CodePudding

I have list of duplicate Transaction ID 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