BEGIN TRY

    BEGIN TRANSACTION;

    -- Drop temp table if exists
    IF EXISTS (
        SELECT 1
        FROM sys.objects
        WHERE object_id = OBJECT_ID(N'[dbo].[wko_ref_temp_table]')
          AND type IN (N'U')
    )
    BEGIN
        DROP TABLE [dbo].[wko_ref_temp_table];
    END

    -- Create temp table
    CREATE TABLE [dbo].[wko_ref_temp_table] (
        [ref_RowID]      NUMERIC(12,0) NULL,
        [file_name]      VARCHAR(100) NOT NULL,
        [attachment]     IMAGE NULL,
        [new_attachment] IMAGE NULL,
        [status]         VARCHAR(50) NULL
    );

    -- Insert original data
    INSERT INTO [dbo].[wko_ref_temp_table] (
        ref_RowID,
        file_name,
        attachment,
        status
    )
    SELECT
        RowID,
        file_name,
        attachment,
        'PENDING'
    FROM [dbo].[wko_ref]
    WHERE column2 IN ('SIGN','RESPONSE_SIGN')

    -- Verify inserted count
    SELECT 
        COUNT(*) AS TotalInserted
    FROM [dbo].[wko_ref_temp_table];

    -- Site-wise count
    SELECT
        a.site_cd,
        COUNT(*) AS total_count
    FROM [dbo].[wko_ref] a
    INNER JOIN [dbo].[wko_ref_temp_table] b
        ON a.RowID = b.ref_RowID
    GROUP BY a.site_cd
    ORDER BY COUNT(*) ASC, a.site_cd ASC;

    -- Preview data
    SELECT TOP 20 *
    FROM [dbo].[wko_ref_temp_table];

    COMMIT TRANSACTION;

    PRINT 'Temp table created and data inserted successfully.';

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT 'Error occurred.';
    PRINT ERROR_MESSAGE();
    PRINT ERROR_LINE();
    PRINT ERROR_PROCEDURE();

END CATCH;