Here's another way to do this in a batch (no cursors). @KM's looks like it should work but it looks a little slow/scary to me with lots of locking and scans involved; if you restrict the working set to only the new rows then it should be pretty fast.
Here's the setup script for the test data:
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)
So we have a 1:Many and we want to make this a 1:1. To do this, first queue up a list of updates (we'll index this over some other set of unique columns to speed up merging later):
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
The result will have one row for every marker that needs to get a new colour. Then insert the new colours and capture the full output:
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;
And finally merge it (here's where that extra index on the temp table comes in handy):
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
This should be very efficient because it only ever has to query the production tables once. Everything else will be operating on the relatively small data in the temp tables.
Test the results:
SELECT m.MarkerID, m.MarkerName, c.ColorID, c.ColorName
FROM Markers m
INNER JOIN Colors c
ON c.ColorID = m.ColorID
Here's our output:
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
This should be what you want, right? No cursors, no serious ugliness. If it chews up too much memory or tempdb space then you can replace the temp table / table variable with an indexed physical staging table. Even with several million rows, there's no way this should fill up the transaction log and crash.