Add increment nos - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add increment nos

Author

Topic

jasonsy
Starting Member

11 Posts

Posted - 2010-03-08 : 03:14:36

I have an existing table and I want to number them from 00001..nnnnn using a column. how can i do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 03:23:56

Do you want to update existing column or want to create a new column with the data?

Madhivanan

Failing to plan is Planning to fail

jasonsy
Starting Member

11 Posts

Posted - 2010-03-08 : 04:13:21

I just want to update an existing column. Thanks.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-08 : 04:38:32

This is what you want or something else...

USE tempdb
IF EXISTS ( SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('#table') )
BEGIN
DROP TABLE #table
END
GO
CREATE TABLE #table (RowNum VARCHAR(10), ID int, Name varchar(20),Salary Numeric(18,2) )
GO
INSERT INTO #table
SELECT '', 1,'A',100
UNION ALL
SELECT '', 2,'B',5000
UNION ALL
SELECT '', 3,'C',9000
UNION ALL
SELECT '', 4,'D',4000
UNION ALL
SELECT '', 5,'E',6000
GO

SELECT * FROM #table

GO

UPDATE #table SET RowNum = RIGHT('0000' + CONVERT(VARCHAR, s.rownum), 5)
FROM #table t
INNER JOIN
(
SELECT ID, Row_Number() OVER (ORDER BY (SELECT 1) ) RowNum FROM #table
) s
ON t.Id = s.ID

GO

SELECT * FROM #table

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-08 : 05:51:09

Note that above solution will only work from version 2005 onwards

Madhivanan

Failing to plan is Planning to fail

jasonsy
Starting Member

11 Posts

Posted - 2010-03-08 : 23:17:38

I only need to update an existing blank column with chronological numbers with padded zeroes for example

acc ctr
11111 00001
22222 00002
and so on.
thanks.

jasonsy
Starting Member

11 Posts

Posted - 2010-03-11 : 00:39:38

i got it. thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-11 : 02:07:06

quote:
Originally posted by jasonsy

i got it. thanks.

Post the workable solution

Madhivanan

Failing to plan is Planning to fail

jasonsy
Starting Member

11 Posts

Posted - 2010-03-11 : 02:58:29

This is the solution.

declare @intCounter int
set @intCounter = 0
update SvAccTemp
SET @intCounter = Ctr = @intCounter + 1
Where DrAmt > 0

SELECT * FROM SvAccTemp
GO
UPDATE SvAccTemp SET LineNumber = RIGHT('0000' + CONVERT(VARCHAR, Ctr), 5)
Where DrAmt > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources