基于同表数据的SQL Update需求：合并语句实现跨年度预算值复制

Hey there! Let's figure out how to copy last year's budget records to this year's version in the same budget table, matching on account and workorder. Since you're on SQL Server 2012 running in 2008 compatibility mode, we'll stick to syntax that works reliably there.

Basic Insert-Select Approach (Safe for New Records) #

This method pulls last year's data, swaps the year version to this year, and inserts it—while skipping any combinations that already exist for this year to avoid duplicates or key errors.

First, replace the placeholder values (like 2023 for last year, 2024 for this year) with your actual year version values, and list all the fields you need to copy:

-- Insert last year's budget data into this year's version
INSERT INTO budget (account, workorder, year_version, budget_amount, [your_other_fields])
SELECT 
    account,
    workorder,
    2024 AS year_version, -- Set to your target year
    budget_amount,
    [your_other_fields] -- Include all other columns you want to copy
FROM budget
WHERE year_version = 2023 -- Source is last year's data
-- Prevent duplicates: only insert if this account+workorder doesn't exist for 2024
AND NOT EXISTS (
    SELECT 1
    FROM budget b_target
    WHERE b_target.account = budget.account
      AND b_target.workorder = budget.workorder
      AND b_target.year_version = 2024
);

MERGE Alternative (For Insert + Update Scenarios) #

If you also need to update existing this-year records with last year's data (not just insert new ones), MERGE works well here too—just make sure to adjust the logic to fit your needs:

MERGE INTO budget AS target
USING (
    -- Get all last year's records to copy/update
    SELECT account, workorder, budget_amount, [your_other_fields]
    FROM budget
    WHERE year_version = 2023
) AS source
-- Match on account+workorder for this year's version
ON target.account = source.account
   AND target.workorder = source.workorder
   AND target.year_version = 2024
-- Insert if no match exists
WHEN NOT MATCHED THEN
    INSERT (account, workorder, year_version, budget_amount, [your_other_fields])
    VALUES (source.account, source.workorder, 2024, source.budget_amount, source.[your_other_fields])
-- Optional: Uncomment below to update existing records with last year's data
-- WHEN MATCHED THEN
--     UPDATE SET 
--         budget_amount = source.budget_amount,
--         [your_other_fields] = source.[your_other_fields];

Quick Tips to Avoid Issues #

• Test First: Before running the insert, replace INSERT INTO ... with just the SELECT part to preview exactly what data will be inserted. This helps catch mistakes early.
• Dynamic Years: If you don't want hardcoded years, use YEAR(GETDATE())-1 for last year and YEAR(GETDATE()) for this year (adjust if your fiscal year doesn't align with calendar year).
• Check Constraints: Make sure your table doesn't have unique constraints or primary keys that would block the insert (the NOT EXISTS clause should handle this, but double-check!).

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