Click here to Skip to main content
Click here to Skip to main content

Tagged as

T-SQL Auto Increment

, 3 Apr 2010
Rate this:
Please Sign up or sign in to vote.
:thumbsup: On Updatehttp://haacked.com/archive/2004/02/28/sql-auto-increment.aspx[^] :thumbsup: On Inserthttp://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: --...
Thumbs Up | :thumbsup: On Update
http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx[^]

Thumbs Up | :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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ilka Guigova
Software Developer
Canada Canada

Comments and Discussions

 
GeneralReason for my vote of 2 An explanation is missing PinadminElina Blank10-Jun-10 17:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 3 Apr 2010
Article Copyright 2010 by Ilka Guigova
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid