Friday, September 16, 2011

SQL Sequence / Auto Increments (Sample Code)

For documentation purposed.
So, I can open this article and being remembered again :)
For update increment number, such as line number, counter, etc. into table column "Counter" :
DECLARE @i As int
SET @i = 1

UPDATE USERS
SET @i= Counter = @i+1
For update table, in sequence (example):
SELECT counter = 0, * FROM Users INTO #tmp_Users
Should be
SELECT counter = 0, * INTO #tmp_Users FROM Users
 DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1
where "counter" is a column name in table "#tmp_user"

Doing the same (on the above) thing with the other method in SELECT statement :
We want something like this:
SELECT {increment value} as Counter, strUsername FROM Users

Counter   strUsername
----------------------
1             John
2             Mike
3             Laura
----------------------

Here is the solution:

CREATE TABLE #user
(
UserID int,
Username varchar(255)
)

INSERT INTO #user VALUES (10, 'John')
INSERT INTO #user VALUES (20, 'Mike')
INSERT INTO #user VALUES (30, 'Laura')
SELECT (
SELECT COUNT(UserID) + 1 AS Counter
FROM #user usr
WHERE usr.UserID < #user.UserID
) AS Counter, Username
FROM #user
For more information, we can all browse in here:

SQL SEQUENCE/AUTO INCREMENTS: RECOMMENDED ARTICLE/TUTOR
CLICK HERE :

Thanks.
Is there any wrong statement, reference or anything else in this article, I'm sorry and please let me know.
I just want to share what I knew :D

2 comments: