T-SQL Auto Increment

: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:

-- Consider a list of nodes grouped by DOMAIN_ID as stored in the following temporary table:
CREATE TABLE #NODE 
( 
    ID INT IDENTITY, 
    DOMAIN_ID INT,
    NAME NVARCHAR(MAX)
)

-- Let DOMAIN_ID = 100 represent job nodes, DOMAIN_ID = 500 represent personnel nodes
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')

-- Consider a relationship between nodes of different domains as defined in the following temporary table:
CREATE TABLE #NODE_LINKS 
( 
    ID INT IDENTITY,
    RANK INT,  -- a placeholder for additional logic to rank links and a candidate for an auto-increment input
    LEFT_NODE_ID INT,
    RIGHT_NODE_ID INT
)

-- Let's link personnel to jobs:
-- Each person in personnel is assigned a position/rank within a job. The position / rank is reset for each job.
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

-- Display the job-personnel links with a counter (a candidate for auto-increment output):
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS COUNTER, * FROM #NODE_LINKS ORDER BY LEFT_NODE_ID

-- Cleanup
DROP TABLE #NODE_LINKS
DROP TABLE #NODE

/* Output:
COUNTER	ID	RANK	LEFT_NODE_ID	RIGHT_NODE_ID
1	1	0	1		3
2	2	1	1		4
3	3	2	1		5
4	4	0	2		3
5	5	1	2		4
6	6	2	2		5
*/

Good Luck!