:thumbsup: On Update
http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx[
^]
:thumbsup: On Insert
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e021ead3-5dd4-4f2b-a79e-a9258384f313[
^]
To illustrate the use of auto-increment, here is a dummy example:
CREATE TABLE #NODE
(
ID INT IDENTITY,
DOMAIN_ID INT,
NAME NVARCHAR(MAX)
)
INSERT INTO #NODE VALUES (100, 'Bloggers')
INSERT INTO #NODE VALUES (100, 'Vloggers')
INSERT INTO #NODE VALUES (500, 'Joe')
INSERT INTO #NODE VALUES (500, 'Jane')
INSERT INTO #NODE VALUES (500, 'Bob')
CREATE TABLE #NODE_LINKS
(
ID INT IDENTITY,
RANK INT,
LEFT_NODE_ID INT,
RIGHT_NODE_ID INT
)
DECLARE @RANK_OFFSET INT
SELECT @RANK_OFFSET = COUNT(ID) FROM #NODE WHERE DOMAIN_ID = 500
INSERT INTO #NODE_LINKS (RANK, LEFT_NODE_ID, RIGHT_NODE_ID)
SELECT (ROW_NUMBER() OVER (ORDER BY Jobs.ID) - 1) % @RANK_OFFSET, Jobs.ID, Personnel.ID
FROM #NODE Jobs
JOIN #NODE Personnel ON Personnel.DOMAIN_ID = 500
WHERE Jobs.DOMAIN_ID = 100
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS COUNTER, * FROM #NODE_LINKS ORDER BY LEFT_NODE_ID
DROP TABLE #NODE_LINKS
DROP TABLE #NODE
Good Luck!