MSSQL 2014：获取所有客户及其最后订单用于数据迁移

第一步：编写获取客户+最后订单的SQL查询 #

因为要包含所有客户（哪怕没有订单的），咱们用LEFT JOIN结合窗口函数ROW_NUMBER()来精准定位每个客户的最新订单，这种写法在MSSQL 2014里完全支持：

WITH CustomerLatestOrders AS (
    SELECT 
        c.CustomerID,
        c.CustomerName, -- 替换成你实际的客户表字段
        o.OrderID,
        o.OrderDate,
        o.TotalAmount, -- 替换成你实际的订单表字段
        -- 按客户分组，按订单日期倒序排，最新的订单排第1
        ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC, o.OrderID DESC) AS RowNum
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
)
SELECT 
    CustomerID,
    CustomerName,
    OrderID,
    OrderDate,
    TotalAmount
FROM CustomerLatestOrders
WHERE RowNum = 1 OR OrderID IS NULL -- 保留没有订单的客户
ORDER BY CustomerID;

说明：如果同一客户在同一时间有多笔订单，OrderID DESC确保取最新创建的那笔（假设OrderID是自增主键），你可以根据实际业务调整排序字段。

第二步：用游标遍历结果并迁移到目标数据库 #

因为数据量小，游标完全够用。下面是完整的迁移代码，记得替换成你实际的目标数据库名称、目标表结构和字段映射：

-- 声明变量存储游标读取的数据
DECLARE @CustomerID INT,
        @CustomerName NVARCHAR(100),
        @OrderID INT,
        @OrderDate DATETIME,
        @TotalAmount DECIMAL(18,2);

-- 声明游标，关联第一步的查询结果
DECLARE CustomerMigrationCursor CURSOR FOR
WITH CustomerLatestOrders AS (
    SELECT 
        c.CustomerID,
        c.CustomerName,
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY o.OrderDate DESC, o.OrderID DESC) AS RowNum
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
)
SELECT 
    CustomerID,
    CustomerName,
    OrderID,
    OrderDate,
    TotalAmount
FROM CustomerLatestOrders
WHERE RowNum = 1 OR OrderID IS NULL
ORDER BY CustomerID;

-- 打开游标
OPEN CustomerMigrationCursor;

-- 读取第一行数据
FETCH NEXT FROM CustomerMigrationCursor INTO 
    @CustomerID, @CustomerName, @OrderID, @OrderDate, @TotalAmount;

-- 循环处理每一行
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 插入到目标数据库的表中（替换成你的目标库、Schema和表名）
    INSERT INTO TargetDB.dbo.TargetCustomerOrders (
        TargetCustomerID,
        TargetCustomerName,
        TargetOrderID,
        TargetOrderDate,
        TargetTotalAmount
    )
    VALUES (
        @CustomerID,
        @CustomerName,
        @OrderID,
        @OrderDate,
        @TotalAmount
    );

    -- 读取下一行
    FETCH NEXT FROM CustomerMigrationCursor INTO 
        @CustomerID, @CustomerName, @OrderID, @OrderDate, @TotalAmount;
END;

-- 关闭并释放游标
CLOSE CustomerMigrationCursor;
DEALLOCATE CustomerMigrationCursor;

PRINT '数据迁移完成！';

注意事项 #

• 请确保执行这段代码的账号同时拥有源数据库的读取权限和目标数据库的写入权限。
• 如果目标表的字段类型和源表不一致，记得做相应的类型转换（比如CAST(@OrderDate AS DATE)如果目标表是DATE类型）。
• 因为数据量小，不用考虑批量插入优化，逐行处理完全没问题。

内容的提问来源于stack exchange，提问作者tomek-a