sql - 使用新表 id 更新事务表数据

Question

我有 3 个具有以下结构和数据的表。

CREATE TABLE [dbo].[Department]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[Department_New]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[Employee]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DepId] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL
)

SET IDENTITY_INSERT [dbo].[Department] ON 

INSERT [dbo].[Department] ([Id], [Name]) 
VALUES (1, N'HR'), (2, N'IT'), (3, N'Account'), (4, N'Finance'), (5, N'Software') 

SET IDENTITY_INSERT [dbo].[Department] OFF

SET IDENTITY_INSERT [dbo].[Department_New] ON 

INSERT [dbo].[Department_New] ([Id], [Name]) 
VALUES (1, N'IT'), (2, N'HR'), (3, N'Software'), (4, N'Account'), 
       (5, N'iDontKnow'), (6, N'Finance') 

SET IDENTITY_INSERT [dbo].[Department_New] OFF

SET IDENTITY_INSERT [dbo].[Employee] ON  

INSERT [dbo].[Employee] ([Id], [DepId], [Name]) 
VALUES (1, 1, N'abc'), (2, 2, N'zxc'), (3, 1, N'xcv'),
       (4, 3, N'cvb'), (5, 4, N'vbn'), (6, 1, N'bnm'),
       (7, 3, N'asd'), (8, 3, N'sdf'), (9, 2, N'dfg'),
       (10, 4, N'fgh'), (11, 5, N'ghj'), (12, 5, N'hjk'),
       (13, 6, N'jkl'), (14, 6, N'qwe'), (15, 2, N'wre'),
       (16, 3, N'ert'), (17, 6, N'rty'), (18, 1, N'tyu') 

SET IDENTITY_INSERT [dbo].[Employee] OFF

截至目前 Employee 表有旧的 depId。我只想用新的 depId 更新。我不能写 for 循环，因为员工表包含超过 1,000,000 行数据。

我试过的

CREATE PROCEDURE UpdateDepId
AS
BEGIN
    DECLARE @totalRecords INT
    DECLARE @I INT

    SELECT @I = 1

    SELECT @totalRecords = COUNT(DISTINCT DepId) FROM Employee

    DECLARE @DPID INT
    DECLARE @Id INT

    --drop table #Temp
    IF OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL
         DROP TABLE #Temp; 

    CREATE TABLE #Temp
    (
        Id INT IDENTITY(1,1),
        DepId INT
    )

    INSERT INTO #Temp 
        SELECT DISTINCT DepId  
        FROM Employee 
        ORDER BY DepId

    WHILE (@I <= @totalRecords)
    BEGIN
        SET @DPID = (SELECT DepId FROM #Temp WHERE Id = @I)
        EXEC UpdateDepByDepId @DPID

        SELECT @I = @I + 1
    END
    DROP TABLE #Temp
 END

CREATE PROCEDURE UpdateDepByDepId
    @DepId INT
AS
BEGIN
    DECLARE @DepIdNew INT
    SET @DepIdNew = (SELECT Id FROM Department_New 
                     WHERE Name IN (SELECT Name FROM Department WHERE id = @DepId))

    SELECT @DepIdNew AS NewDepId, @DepId AS OldDepId

    IF ISNULL(@DepIdNew, 0) <> 0
    BEGIN
        UPDATE Employee 
        SET DepId = @DepIdNew 
        WHERE DepId = @DepId
    END
 END

但这对我不起作用。请帮忙

标签： sqlsql-servertsql

Answer 1

使用一个update和from：

update e
    set depid = dn.id
    from employee e join
         department d
         on e.depid = d.id join
         department_new dn
         on dn.name = d.name;