SQL Server 2017如何动态添加源表中不存在的多列？

Got it, I totally get your frustration with the hardcoded approach—you want a script that automatically detects which columns are missing in Table2 compared to Table1 and adds them without manual updates. Let's build that dynamic solution.

The Core Idea #

We'll use the INFORMATION_SCHEMA.COLUMNS system view to compare the columns between the two tables, generate the necessary ALTER TABLE statements dynamically, and then execute them. This way, you don't have to list columns or data types manually—everything pulls directly from Table1's schema.

Working Script #

DECLARE @AlterScript NVARCHAR(MAX) = ''

-- Build the ALTER TABLE statements for missing columns
SELECT @AlterScript += 
    'ALTER TABLE Table2 ADD [' + c.COLUMN_NAME + '] ' + 
    c.DATA_TYPE + 
    -- Handle length for string data types (varchar, nvarchar, etc.)
    CASE 
        WHEN c.DATA_TYPE IN ('varchar', 'char', 'nvarchar', 'nchar') THEN 
            '(' + CASE WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) END + ')' 
        ELSE '' 
    END + 
    ' NULL;'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'Table1'
-- Exclude columns that already exist in Table2
AND NOT EXISTS (
    SELECT 1 
    FROM INFORMATION_SCHEMA.COLUMNS t2_cols
    WHERE t2_cols.TABLE_NAME = 'Table2'
      AND t2_cols.COLUMN_NAME = c.COLUMN_NAME
)

-- Only run the script if there are columns to add
IF @AlterScript <> ''
BEGIN
    EXEC sp_executesql @AlterScript
END

How It Works #

• Column Comparison: The query checks every column in Table1, and only includes columns that aren't present in Table2.
• Data Type Matching: It preserves the exact data type from Table1, including handling string lengths (like varchar(50) or nvarchar(MAX)).
• Safe Execution: The script only runs if there are missing columns—no unnecessary ALTER TABLE calls if Table2 is already in sync.

Notes #

• If you need columns to be NOT NULL instead of NULL, adjust the script accordingly, but I recommend starting with NULL to avoid errors if Table2 already has rows.
• This works for most common data types—if you're using specialized types (like XML, DATE, or custom types), the script will still handle them since it pulls directly from DATA_TYPE.
• You can schedule this script to run periodically if you need to keep Table2 in sync with Table1 automatically.

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