Sybase逐行处理临时表数据并生成结果至另一临时表的实现方案

Hey there! Let's break down how to solve this Sybase task—whether you want a straightforward approach or want to experiment with recursion, we've got you covered. First, let's assume some sample table structures to make the examples concrete (adjust these to match your actual schema).

First, let's formalize the source and results temp tables (tweak columns to fit your data):

-- Source temp table (you already have this populated with up to 10 rows)
CREATE TABLE #SourceData (
    RowID INT IDENTITY(1,1) PRIMARY KEY, -- Auto-increment ID to track rows
    KeyIdentifier VARCHAR(50) -- Example column: your AB1-style key to validate
    -- Add other source columns as needed
)

-- Results temp table to store validation outcomes
CREATE TABLE #ValidationResults (
    RowID INT,
    KeyIdentifier VARCHAR(50),
    Status VARCHAR(10), -- 'Pass' or 'Fail'
    Comment VARCHAR(255) -- Context for the status
)

Since you only have up to 10 rows, a set-based query is the most efficient and simple way—no need for loops or recursion. This handles all rows in one go:

INSERT INTO #ValidationResults (RowID, KeyIdentifier, Status, Comment)
SELECT
    sd.RowID,
    sd.KeyIdentifier,
    -- Replace this CASE logic with your actual validation condition
    CASE
        WHEN EXISTS (
            SELECT 1 
            FROM YourTargetDatabase.dbo.YourTargetTable tt
            WHERE tt.MatchingColumn = sd.KeyIdentifier -- Your validation rule
        ) THEN 'Pass'
        ELSE 'Fail'
    END AS Status,
    CASE
        WHEN EXISTS (
            SELECT 1 
            FROM YourTargetDatabase.dbo.YourTargetTable tt
            WHERE tt.MatchingColumn = sd.KeyIdentifier
        ) THEN 'Validation passed: matching record exists'
        ELSE 'Validation failed: no matching record found'
    END AS Comment
FROM #SourceData sd

This is the best choice for small datasets—it's fast, readable, and avoids the overhead of row-by-row processing.

Yes, you can use recursion in Sybase (ASE 15 and later supports recursive CTEs). This mimics "逐行" processing by leveraging the auto-increment RowID:

WITH RecursiveValidator AS (
    -- Anchor member: Start with the first row
    SELECT
        RowID,
        KeyIdentifier,
        CASE
            WHEN EXISTS (
                SELECT 1 FROM YourTargetTable tt WHERE tt.MatchingColumn = KeyIdentifier
            ) THEN 'Pass'
            ELSE 'Fail'
        END AS Status,
        CASE
            WHEN EXISTS (
                SELECT 1 FROM YourTargetTable tt WHERE tt.MatchingColumn = KeyIdentifier
            ) THEN 'Record found'
            ELSE 'No match'
        END AS Comment
    FROM #SourceData
    WHERE RowID = 1

    UNION ALL

    -- Recursive member: Fetch the next row each time
    SELECT
        sd.RowID,
        sd.KeyIdentifier,
        CASE
            WHEN EXISTS (
                SELECT 1 FROM YourTargetTable tt WHERE tt.MatchingColumn = sd.KeyIdentifier
            ) THEN 'Pass'
            ELSE 'Fail'
        END AS Status,
        CASE
            WHEN EXISTS (
                SELECT 1 FROM YourTargetTable tt WHERE tt.MatchingColumn = sd.KeyIdentifier
            ) THEN 'Record found'
            ELSE 'No match'
        END AS Comment
    FROM #SourceData sd
    JOIN RecursiveValidator rv ON sd.RowID = rv.RowID + 1
)
-- Insert recursive results into the temp table
INSERT INTO #ValidationResults (RowID, KeyIdentifier, Status, Comment)
SELECT RowID, KeyIdentifier, Status, Comment
FROM RecursiveValidator
ORDER BY RowID

Note: Recursion here is more of a technical exercise than a practical solution for 10 rows—set-based is still better. But this proves it's possible!

If your validation requires multiple steps per row (e.g., sequential checks that can't be done in a single query), a cursor works:

DECLARE @RowID INT, @KeyID VARCHAR(50), @Status VARCHAR(10), @Comment VARCHAR(255)

-- Declare cursor to loop through source rows
DECLARE validation_cursor CURSOR FOR
SELECT RowID, KeyIdentifier FROM #SourceData ORDER BY RowID

OPEN validation_cursor
FETCH NEXT FROM validation_cursor INTO @RowID, @KeyID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Run your custom validation logic here
    IF EXISTS (SELECT 1 FROM YourTargetTable tt WHERE tt.MatchingColumn = @KeyID)
    BEGIN
        SET @Status = 'Pass'
        SET @Comment = 'Validation succeeded'
    END
    ELSE
    BEGIN
        SET @Status = 'Fail'
        SET @Comment = 'Validation failed: no matching record'
    END

    -- Insert result into temp table
    INSERT INTO #ValidationResults (RowID, KeyIdentifier, Status, Comment)
    VALUES (@RowID, @KeyID, @Status, @Comment)

    FETCH NEXT FROM validation_cursor INTO @RowID, @KeyID
END

-- Clean up cursor
CLOSE validation_cursor
DEALLOCATE validation_cursor

• Use set-based queries for simple validation with small datasets (most efficient).
• Recursion works but is overkill here—save it for hierarchical data or larger datasets where row-by-row logic is needed and recursion adds value.
• Cursors are best reserved for complex, multi-step validation that can't be expressed in a single set-based query.

内容的提问来源于stack exchange，提问作者Newbie