以下是一种批处理的方法（无游标）来完成此操作。@KM的方法看起来应该可以工作，但涉及大量的锁定和扫描，这让我感觉有些慢/可怕。如果将工作集限制为仅包含新行，则速度应该很快。
以下是测试数据的设置脚本：
CREATE TABLE Colors
(
ColorID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
ColorName varchar(50) NOT NULL
)
CREATE TABLE Markers
(
MarkerID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MarkerName varchar(50) NOT NULL,
ColorID int NOT NULL,
CONSTRAINT FK_Markers_Colors FOREIGN KEY (ColorID)
REFERENCES Colors (ColorID)
)
INSERT Colors (ColorName) VALUES ('Red')
INSERT Colors (ColorName) VALUES ('Green')
INSERT Colors (ColorName) VALUES ('Blue')
INSERT Markers (MarkerName, ColorID) VALUES ('Test1', 1)
INSERT Markers (MarkerName, ColorID) VALUES ('Test2', 1)
INSERT Markers (MarkerName, ColorID) VALUES ('Test3', 1)
INSERT Markers (MarkerName, ColorID) VALUES ('Test4', 2)
INSERT Markers (MarkerName, ColorID) VALUES ('Test5', 2)
INSERT Markers (MarkerName, ColorID) VALUES ('Test6', 3)
INSERT Markers (MarkerName, ColorID) VALUES ('Test7', 3)
我们有一个1：Many的关系，现在想要将其变为1：1。为了实现这个目标，首先需要列出一份更新列表（我们将对其进行索引，使用一些其他唯一列来加速后续合并操作）：
CREATE TABLE #NewColors
(
MarkerID int NOT NULL,
ColorName varchar(50) NOT NULL,
Seq int NOT NULL,
CONSTRAINT PK_#NewColors PRIMARY KEY (MarkerID)
)
CREATE INDEX IX_#NewColors
ON #NewColors (ColorName, Seq);
WITH Refs AS
(
SELECT
MarkerID,
ColorID,
ROW_NUMBER() OVER (PARTITION BY ColorID ORDER BY (SELECT 1)) AS Seq
FROM Markers
)
INSERT #NewColors (MarkerID, ColorName, Seq)
SELECT r.MarkerID, c.ColorName, r.Seq - 1
FROM Refs r
INNER JOIN Colors c
ON c.ColorID = r.ColorID
WHERE r.Seq > 1
结果将为每个需要获得新颜色的标记生成一行。然后插入新颜色并捕获完整输出：
DECLARE @InsertedColors TABLE
(
ColorID int NOT NULL PRIMARY KEY,
ColorName varchar(50) NOT NULL
)
INSERT Colors (ColorName)
OUTPUT inserted.ColorID, inserted.ColorName
INTO @InsertedColors
SELECT ColorName
FROM #NewColors nc;
最后将其合并（这就是临时表上额外索引的好处所在）：
WITH InsertedColorSeq AS
(
SELECT
ColorID, ColorName,
ROW_NUMBER() OVER (PARTITION BY ColorName ORDER BY ColorID) AS Seq
FROM @InsertedColors
),
Updates AS
(
SELECT nc.MarkerID, ic.ColorID AS NewColorID
FROM #NewColors nc
INNER JOIN InsertedColorSeq ic
ON ic.ColorName = nc.ColorName
AND ic.Seq = nc.Seq
)
MERGE Markers m
USING Updates u
ON m.MarkerID = u.MarkerID
WHEN MATCHED THEN
UPDATE SET m.ColorID = u.NewColorID;
DROP TABLE #NewColors
这应该非常高效，因为它只需要查询生产表一次。其他所有操作都将在相对较小的临时表中进行。
测试结果：
SELECT m.MarkerID, m.MarkerName, c.ColorID, c.ColorName
FROM Markers m
INNER JOIN Colors c
ON c.ColorID = m.ColorID
这是我们的输出结果：
MarkerID MarkerName ColorID ColorName
1 Test1 1 Red
2 Test2 6 Red
3 Test3 7 Red
4 Test4 2 Green
5 Test5 5 Green
6 Test6 3 Blue
7 Test7 4 Blue
这应该是您想要的，对吧？没有游标，没有严重的丑陋。如果它消耗太多内存或tempdb空间，则可以将临时表/表变量替换为索引物理暂存表。即使有数百万行，也不可能填满事务日志并崩溃。
INSERT和UPDATE语句完成的任务上。 - Aaronaught