Click here to Skip to main content
14,767,556 members
Articles » Database » Database » General
Tip/Trick
Posted 2 Apr 2010

Tagged as

Stats

31.3K views
3 bookmarked

T-SQL Auto Increment

Rate me:
Please Sign up or sign in to vote.
3.33/5 (3 votes)
3 Apr 2010CPOL
: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: --...
: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!

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 Pin
Elina Blank10-Jun-10 18:40
sitebuilderElina Blank10-Jun-10 18:40 

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

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