USE ION_Data;
GO

-- Create a temporary table to store the sources
CREATE TABLE #Sources (
    SourceName NVARCHAR(255)
);

-- Insert the sources into the temporary table
INSERT INTO #Sources (SourceName)
VALUES
('my_Correct_meter-RealEnergyTotal'),
('my_second_Correct_Meter-Art1Result2'),

-- Declare a cursor to iterate through the sources
DECLARE @SourceName NVARCHAR(255);
DECLARE @CorrectName NVARCHAR(255);
DECLARE SourceCursor CURSOR FOR
SELECT SourceName FROM #Sources;

-- Open the cursor
OPEN SourceCursor;

-- Fetch the first source
FETCH NEXT FROM SourceCursor INTO @SourceName;

-- Loop through the sources
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Extract the correct name from the incorrect name
    SET @CorrectName = LEFT(@SourceName, CHARINDEX('-', @SourceName) - 1);

    -- Query 1
    BEGIN TRY
        UPDATE DataLog2
        SET SourceID = (SELECT ID FROM Source WHERE Name = @CorrectName)
        WHERE SourceID IN (SELECT ID FROM Source WHERE Name = @SourceName);
        PRINT 'Query 1 executed successfully for ' + @SourceName;
    END TRY
    BEGIN CATCH
        PRINT 'Error executing Query 1 for ' + @SourceName;
    END CATCH;

    -- Query 2
    BEGIN TRY
        EXEC spDM_DeleteSourceAndBatchDeleteData @SourceName;
        PRINT 'Query 2 executed successfully for ' + @SourceName;
    END TRY
    BEGIN CATCH
        PRINT 'Error executing Query 2 for ' + @SourceName;
    END CATCH;

    -- Query 3
    BEGIN TRY
        DELETE FROM [ION_Data].[dbo].[Channel]
        WHERE [RecorderID] IN (SELECT [ID] FROM [ION_Data].[dbo].[Recorder] 
        WHERE [SourceID] IN (SELECT [ID] FROM [ION_Data].[dbo].[Source] 
        WHERE [Name] IN (@CorrectName)));

        DELETE FROM [ION_Data].[dbo].[RecorderState]
        WHERE [RecorderID] IN (SELECT [ID] FROM [ION_Data].[dbo].[Recorder]
        WHERE [SourceID] IN (SELECT [ID] FROM [ION_Data].[dbo].[Source]
        WHERE [Name] IN (@CorrectName)));

        DELETE FROM [ION_Data].[dbo].[Recorder]
        WHERE [ID] IN (SELECT [ID] FROM [ION_Data].[dbo].[Recorder]
        WHERE [SourceID] IN (SELECT [ID] FROM [ION_Data].[dbo].[Source]
        WHERE [Name] IN (@CorrectName)));

        DELETE FROM [ION_Network].[dbo].[IAS_MeasurementAddress]
        WHERE [SourceID] IN (SELECT [SourceID] FROM [ION_Network].[dbo].[SRC_Source]
        WHERE [Name] IN (@CorrectName));
        PRINT 'Query 3 executed successfully for ' + @SourceName;
    END TRY
    BEGIN CATCH
        PRINT 'Error executing Query 3 for ' + @SourceName;
    END CATCH;

    -- Fetch the next source
    FETCH NEXT FROM SourceCursor INTO @SourceName;
END;

-- Close and deallocate the cursor
CLOSE SourceCursor;
DEALLOCATE SourceCursor;

-- Drop the temporary table
DROP TABLE #Sources;
GO