如何将旧库单表拆分至新库双表并合并关联查询？

Hey there! Let's solve this problem cleanly. You need to split your old tblCustomers into two new tables in a single atomic operation, and correctly link the ReservedService entries to their corresponding Customer records using the auto-generated CustomerId. Here's how you can do it in SQL Server:

Solution with Atomic Transaction #

We'll use a table variable to capture the newly generated Customer.Id values along with the original Code from tblCustomers (assuming Code is a unique identifier for each customer—if it's not, use your old table's primary key instead). Then we'll join this captured data back to the old table to populate ReservedService.

USE newDB;

BEGIN TRANSACTION;

BEGIN TRY
    -- Create a temporary storage to hold new Customer IDs and their original codes
    DECLARE @InsertedCustomers TABLE (
        NewCustomerId INT,
        OriginalCode VARCHAR(50) -- Match the data type of oldDB.dbo.tblCustomers.Code
    );

    -- Insert into Customer table and capture the auto-generated IDs
    INSERT INTO dbo.Customer ([Code], [Name], [Address], [Phone])
    OUTPUT inserted.Id, inserted.Code INTO @InsertedCustomers(NewCustomerId, OriginalCode)
    SELECT 
        Code,
        SUBSTRING(Name, 1, 50),
        SUBSTRING(Address, 1, 100),
        SUBSTRING(Phone, 1, 50)
    FROM oldDB.dbo.tblCustomers;

    -- Now populate ReservedService using the captured Customer IDs
    INSERT INTO dbo.ReservedService (CustomerId, Duration, Price)
    SELECT 
        ic.NewCustomerId,
        SUBSTRING(c.Duration, 1, 50),
        SUBSTRING(c.Price, 1, 100)
    FROM oldDB.dbo.tblCustomers c
    INNER JOIN @InsertedCustomers ic 
        ON c.Code = ic.OriginalCode;

    -- Commit if everything worked
    COMMIT TRANSACTION;
    PRINT 'Customer data split into Customer and ReservedService successfully!';
END TRY
BEGIN CATCH
    -- Rollback on any error to avoid partial data
    ROLLBACK TRANSACTION;
    PRINT 'Error during data split: ' + ERROR_MESSAGE();
END CATCH;

Key Notes: #

• Atomicity: The transaction ensures that either both inserts succeed, or neither does—so you won't end up with orphaned Customer records without a corresponding ReservedService entry (or vice versa).
• Unique Identifier: We use Code to link the old and new data. If Code isn't unique in tblCustomers, replace it with the actual primary key of your old table (like an Id column if it exists) to avoid mismatches.
• Data Types: Make sure the data type of OriginalCode in the table variable matches exactly with oldDB.dbo.tblCustomers.Code to prevent conversion errors.

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