使用新的表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循环，因为employee表包含超过1000000行的数据。

我尝试过的

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

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

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;