使用UPDATE命令更新SQL Server列：求original_tbl转update_tbl的T-SQL语句

Hey there! Since you haven't shared the specific structures or exact differences between your original_tbl and update_tbl, I’ll walk you through common T-SQL transformation scenarios with examples that cover most typical use cases. Once you fill in the details of your tables (like column names, data types, or how the data needs to change), I can refine this to match your exact needs!

1. Renaming Columns #

If your target table has different column names than the original, use sp_rename to update them:

-- Rename a single column
EXEC sp_rename 'original_tbl.old_column_name', 'new_column_name', 'COLUMN';

-- Verify the change
SELECT * FROM original_tbl;

Note: sp_rename doesn’t update dependencies like views or stored procedures, so you’ll need to adjust those separately if they reference the old column name.

2. Adding/Removing Columns #

To align the table schema with the target:

-- Add a new nullable column
ALTER TABLE original_tbl
ADD new_column VARCHAR(100) NULL;

-- Add a column with a default value
ALTER TABLE original_tbl
ADD registration_date DATETIME DEFAULT GETDATE();

-- Remove an unused column
ALTER TABLE original_tbl
DROP COLUMN outdated_column;

3. Transforming Existing Data Values #

If you need to clean or reformat data to match the target:

-- Standardize text values (e.g., fix case or replace labels)
UPDATE original_tbl
SET user_role = UPPER(user_role),
    status = CASE 
        WHEN status = 'Active' THEN 'Enabled'
        WHEN status = 'Inactive' THEN 'Disabled'
        ELSE status
    END;

-- Calculate and populate a derived column
UPDATE original_tbl
SET total_cost = quantity * unit_price * (1 - discount_rate);

4. Pivoting Rows to Columns #

If your original table has row-based data that needs to be grouped into columns (e.g., sales by product category):

-- Pivot example: Aggregate sales by customer and product type
SELECT customer_id, [Laptop], [Phone], [Tablet]
FROM (
    SELECT customer_id, product_type, sales_amount
    FROM original_tbl
) AS source_data
PIVOT (
    SUM(sales_amount)
    FOR product_type IN ([Laptop], [Phone], [Tablet])
) AS pivoted_table;

5. Unpivoting Columns to Rows #

If your original table has wide columns that need to be converted into rows:

-- Unpivot example: Turn category columns into rows
SELECT customer_id, product_type, sales_amount
FROM (
    SELECT customer_id, Laptop, Phone, Tablet
    FROM original_tbl
) AS source_data
UNPIVOT (
    sales_amount FOR product_type IN ([Laptop], [Phone], [Tablet])
) AS unpivoted_table;

6. Cleaning Up Duplicate Rows #

If your target table requires unique rows, use a CTE with ROW_NUMBER() to remove duplicates:

WITH DuplicateRecords AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY unique_key_column1, unique_key_column2 
               ORDER BY created_date DESC -- Keep the most recent record
           ) AS row_num
    FROM original_tbl
)
DELETE FROM DuplicateRecords
WHERE row_num > 1;

If you can share:

• The schema of both original_tbl and update_tbl (run SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('original_tbl', 'update_tbl'); and share the output)
• Specific examples of how rows/columns differ (e.g., "I need to combine first_name and last_name into a full_name column" or "Dates in original are stored as VARCHAR and need to be DATETIME")

I can write a tailor-made T-SQL script that exactly matches your transformation needs!

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