如何读取SQL表数据并将不同值插入另一表？求助修正脚本

Hey there! Let's work through fixing that cursor script—there are a handful of small missteps that are keeping it from running correctly. Let's break down the issues first, then jump to the corrected code.

Key Issues in Your Original Script #

• Unused/Incorrect Cursor Variable: The @tercero_usuario1 cursor variable was declared but never properly used, and your FETCH statement tried to write data to it incorrectly.
• Missing INTO in FETCH: Your initial FETCH NEXT didn't specify where to store the cursor's data, and the subsequent FETCH had the wrong target variables.
• Invalid SELECT in Loop: You tried to query directly from the cursor variable (@tercero_usuario) inside the loop, which isn't how cursors work—you should use FETCH INTO to populate your variables instead.
• Null Risk for Primary Key: If TD_TERCERO_CONTACTO was empty, MAX(t.tercero_id) would return NULL, making @pk_tercero_contacto NULL and causing insert failures.

Corrected Cursor Script #

DECLARE 
    @pk_tercero_contacto INT,
    @tercero_id INT,
    @login NVARCHAR(200),
    @defecto BIT,
    @descripcion NVARCHAR(200),
    @notificaciones BIT

-- Handle empty table case to avoid NULL primary key
SELECT @pk_tercero_contacto = ISNULL(MAX(t.pk_tercero_contacto), 0) + 1 
FROM TD_TERCERO_CONTACTO t

-- Declare cursor with your target query
DECLARE tercero_usuario CURSOR FOR
    SELECT t.LOGIN, t.TERCERO_ID, t.DEFECTO, t.DESCRIPCION, t.NOTIFICACIONES
    FROM TD_TERCERO_USUARIO t

OPEN tercero_usuario
-- First fetch to populate variables
FETCH NEXT FROM tercero_usuario INTO @login, @tercero_id, @defecto, @descripcion, @notificaciones

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Insert into target table (explicit column names are safer than relying on order!)
    INSERT INTO TD_TERCERO_CONTACTO 
        (pk_tercero_contacto, login, col3, col4, descripcion, notificaciones, tercero_id, col8, col9, col10, defecto)
    VALUES 
        (@pk_tercero_contacto, @login, NULL, NULL, @descripcion, @notificaciones, @tercero_id, NULL, NULL, NULL, @defecto)

    -- Increment primary key for next row
    SET @pk_tercero_contacto = @pk_tercero_contacto + 1

    -- Fetch next row of data
    FETCH NEXT FROM tercero_usuario INTO @login, @tercero_id, @defecto, @descripcion, @notificaciones
END

-- Clean up cursor
CLOSE tercero_usuario
DEALLOCATE tercero_usuario
GO

Important Notes #

1. Explicit Column Names: I added placeholders like col3, col4—replace these with the actual column names from TD_TERCERO_CONTACTO that correspond to your NULL values. Using explicit column names prevents bugs if the table's column order ever changes.
2. Simpler Option for Auto-Increment Keys: If pk_tercero_contacto is an identity column (auto-incrementing), you don't need to manually manage the primary key at all! Here's a simplified version:

DECLARE 
    @tercero_id INT,
    @login NVARCHAR(200),
    @defecto BIT,
    @descripcion NVARCHAR(200),
    @notificaciones BIT

DECLARE tercero_usuario CURSOR FOR
    SELECT t.LOGIN, t.TERCERO_ID, t.DEFECTO, t.DESCRIPCION, t.NOTIFICACIONES
    FROM TD_TERCERO_USUARIO t

OPEN tercero_usuario
FETCH NEXT FROM tercero_usuario INTO @login, @tercero_id, @defecto, @descripcion, @notificaciones

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO TD_TERCERO_CONTACTO 
        (login, descripcion, notificaciones, tercero_id, defecto)
    VALUES 
        (@login, @descripcion, @notificaciones, @tercero_id, @defecto)
        -- The NULL columns will automatically take NULL or their default values

    FETCH NEXT FROM tercero_usuario INTO @login, @tercero_id, @defecto, @descripcion, @notificaciones
END

CLOSE tercero_usuario
DEALLOCATE tercero_usuario
GO

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