编写Pandas函数提取指定列并更名，实现多源表向目标表追加数据

Got it, let's tackle this problem step by step. Since you want a maintainable way to append data from multiple source tables to your 3-column target table, using a reusable stored procedure is perfect for long-term upkeep—no need to rewrite the same insert logic every time you add a new source table.

Below are examples tailored for two common SQL databases (SQL Server and MySQL), but the core idea applies to most relational databases.

We'll:

• Set up the target table if it doesn't exist
• Create a reusable stored procedure that takes a source table name, transforms its data to match the target schema, and appends it
• Use the procedure to process single or multiple source tables in bulk

Step 1: Create the Target Table #

First, define your target table with the required id, num, and group columns. Note that group is a reserved keyword in most SQL databases, so we'll escape it properly:

SQL Server #

CREATE TABLE TargetTable (
    id INT PRIMARY KEY, -- Adjust data type (e.g., VARCHAR) based on your actual id values
    num VARCHAR(100), -- Match the data type of your source table's col1
    [group] VARCHAR(100) -- Square brackets escape the reserved keyword
);

MySQL #

CREATE TABLE TargetTable (
    id INT PRIMARY KEY,
    num VARCHAR(100),
    `group` VARCHAR(100) -- Backticks escape the reserved keyword
);

Step 2: Build a Reusable Stored Procedure #

This procedure will handle the transformation (mapping col1 to num, col2 to group) and append logic. It accepts a source table name as a parameter, making it flexible for any number of source tables.

SQL Server #

CREATE PROCEDURE AppendSourceToTarget
    @SourceTableName NVARCHAR(128) -- Accepts the name of your source table
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra result sets from interfering with SELECT statements

    -- Use dynamic SQL to safely reference the source table
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'
        -- Insert only new records (adjust the WHERE clause if you need to handle duplicates differently)
        INSERT INTO TargetTable (id, num, [group])
        SELECT id, col1, col2
        FROM ' + QUOTENAME(@SourceTableName) + N'
        WHERE id NOT IN (SELECT id FROM TargetTable);
    ';

    EXEC sp_executesql @SQL; -- Execute the dynamic SQL
END;

MySQL #

DELIMITER // -- Temporarily change the delimiter to avoid conflicts with semicolons
CREATE PROCEDURE AppendSourceToTarget(IN SourceTableName VARCHAR(128))
BEGIN
    SET @SQL = CONCAT(
        'INSERT INTO TargetTable (id, num, `group`) ',
        'SELECT id, col1, col2 FROM ', SourceTableName, ' ',
        'WHERE id NOT IN (SELECT id FROM TargetTable);'
    );
    
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ; -- Reset the delimiter back to semicolon

Step 3: Batch Process Multiple Source Tables #

Process Individual Source Tables #

Call the procedure for each source table you want to sync:

SQL Server

EXEC AppendSourceToTarget 'SourceTable1';
EXEC AppendSourceToTarget 'SourceTable2';
EXEC AppendSourceToTarget 'SourceTable3';

MySQL

CALL AppendSourceToTarget('SourceTable1');
CALL AppendSourceToTarget('SourceTable2');
CALL AppendSourceToTarget('SourceTable3');

Automate Bulk Processing (For Named Source Tables) #

If your source tables follow a naming pattern (e.g., all start with Source_), you can loop through them automatically:

SQL Server

DECLARE @TableName NVARCHAR(128);
-- Create a cursor to iterate over all source tables matching your pattern
DECLARE TableCursor CURSOR FOR
SELECT name FROM sys.tables WHERE name LIKE 'Source_%';

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

-- Loop through each table and run the procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC AppendSourceToTarget @TableName;
    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

Handling Duplicates & Updates #

If your source tables might have existing id values in the target table, you can modify the procedure to either:

• Ignore duplicates (as shown above)
• Update existing records with new values

For example, using MERGE in SQL Server to upsert:

SET @SQL = N'
    MERGE TargetTable AS Target
    USING (SELECT id, col1, col2 FROM ' + QUOTENAME(@SourceTableName) + N') AS Source
    ON Target.id = Source.id
    WHEN NOT MATCHED THEN
        INSERT (id, num, [group]) VALUES (Source.id, Source.col1, Source.col2)
    WHEN MATCHED AND (Target.num != Source.col1 OR Target.[group] != Source.col2) THEN
        UPDATE SET Target.num = Source.col1, Target.[group] = Source.col2;
';

In MySQL, use ON DUPLICATE KEY UPDATE:

SET @SQL = CONCAT(
    'INSERT INTO TargetTable (id, num, `group`) ',
    'SELECT id, col1, col2 FROM ', SourceTableName, ' ',
    'ON DUPLICATE KEY UPDATE num = VALUES(num), `group` = VALUES(`group`);'
);

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