使用脚本将SQL Server旧库省/州数据迁移至新库对应表

现有企业旧系统基于SQL Server搭建，新系统调整了表结构，旧库Nations表数据已迁移至新库Countries表，但新旧库对应国家的ID不匹配。需开发脚本，通过旧库Nations名称与新库Countries名称匹配，获取新库Country ID及旧库Provinces表中的省/州名称，生成临时表后插入新库对应表。

不完整的存储过程脚本 #

USE [Mondo-UAT]
USE [MondoErp-UAT]

GO
/****** Object:  StoredProcedure [dbo].[RPT_JobMonitor_Workshop]    Script Date: 9/27/2022 4:42:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE ProvinceMigration

@OldProvinceName    varchar(50) =null,
@NewCountryName varchar(50) =null,
@OldCountryId   int =null,
@NewCountryId   int =null
 
AS
BEGIN
DECLARE @Temp 
TABLE(
NewCountryId INT,
OldProvinceName varchar(50)
)


BEGIN
    SET @NewCountryId =(SELECT c.id FROM [Mondo-UAT].dbo.Countries c ,[MondoErp-UAT].dbo.Nations n  WHERE n.Code1 = c.Country_Name)
    SET @OldProvinceName = (SELECT c.id FROM [Mondo-UAT].dbo.Countries c ,[MondoErp-UAT].dbo.Nations n  WHERE n.Code1 = c.Country_Name)

    INSERT INTO @Temp(NewCountryId,OldProvinceName) VALUES (@NewCountryId
END

已有的关联查询语句 #

旧库Provinces与Nations关联查询 #

SELECT  P.Name, 
        P.Code1,
        P.IDNation,
        N.IDNation, 
        N.Code1
  FROM [MondoErp-UAT].[dbo].[Provinces] P
  LEFT JOIN Nations N ON P.IDNation = N.IDNation

新库Countries表查询 #

SELECT NC.Country_Name
FROM [Mondo-UAT].[dbo].[Countries] NC
order by NC.Country_Name

修正后的完整存储过程 #

USE [MondoErp-UAT]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE ProvinceMigration
AS
BEGIN
    -- 创建临时表存储匹配结果
    DECLARE @Temp TABLE(
        NewCountryId INT,
        OldProvinceName VARCHAR(50)
    )

    -- 关联三张表，批量获取匹配的新国家ID和旧省名
    INSERT INTO @Temp(NewCountryId, OldProvinceName)
    SELECT 
        c.id AS NewCountryId,
        P.Name AS OldProvinceName
    FROM 
        [MondoErp-UAT].dbo.Provinces P
    INNER JOIN 
        [MondoErp-UAT].dbo.Nations N ON P.IDNation = N.IDNation
    INNER JOIN 
        [Mondo-UAT].dbo.Countries c ON N.Code1 = c.Country_Name
    -- 按需添加筛选条件，例如：WHERE N.Code1 = 'CN'

    -- 将临时表数据插入新库目标表（替换为实际表名）
    INSERT INTO [Mondo-UAT].dbo.Provinces_New(CountryId, ProvinceName)
    SELECT NewCountryId, OldProvinceName
    FROM @Temp
END
GO

关键修正说明 #

• 移除不必要的输入参数，适配批量迁移场景
• 使用INNER JOIN关联三张表，一次性获取所有匹配数据，避免单变量赋值的多行返回错误
• 修正OldProvinceName的赋值逻辑，改为取旧库Provinces表的Name字段
• 补充临时表数据插入新库目标表的语句（需替换为实际业务表名）
• 移除重复USE语句，确保存储过程执行上下文正确

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