Update rows with increasing number

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update rows with increasing number

Author

Topic

arcticpro
Starting Member

6 Posts

Posted - 2012-09-27 : 19:51:13

Hi, I have a long list of employees in a table (Employees) and an empty column for the EmployeeID number. What SQL statement can I run to assign an EmployeeID number to every row increasing by 1. So my first employee would have EmployeeID 10000 and it increases by 1 from there. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-27 : 22:54:37

on what order you want to insert number? on alphabetical order of employee names?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arcticpro
Starting Member

6 Posts

Posted - 2012-09-28 : 12:02:24

No particular order is needed. They just need a unique # assigned. If you need an order, then by "LastName" is fine. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 12:38:28

then you can do like this


ALTER TABLE Employees DROP COLUMN EmployeeID
GO
ALTER TABLE Employees ADD EmployeeID int IDENTITY(10000,1) NOT NULL
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arcticpro
Starting Member

6 Posts

Posted - 2012-09-28 : 12:52:56

Thank you. Is there a way to do it without having it be the identity column? Something like update rows in a loop, incrementing 1 each time?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 13:43:15

then how will you maintain it going forward?
making it identity will make sure values get generated automatically in incremental way

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 14:42:36

Setting aside the question of how you will update it going forward, this would have been much easier if you could use an identity column as Visakh suggested, or if you were on SQL 2005 or later. Even on SQL 2000, if one could guarantee that there will not be multiple people with the same names, it would be easier.

Given all that, here is some sample code that can be used to do what you are trying to do in SQL 2000. However, I don't have SQL 2000, so I have not tested it.

CREATE TABLE #tmp (lastname VARCHAR(20), id INT);
INSERT INTO #tmp (lastname) VALUES ('x'),('c'),('ra'),('bxx'),('1');


ALTER TABLE #tmp ADD guidCol UNIQUEIDENTIFIER;
UPDATE #tmp SET guidCol = NEWID();

UPDATE t1 SET 
	id = t2.N
FROM
	#tmp t1
	INNER JOIN 
	(
		SELECT
			a.GuidCol,
			( SELECT COUNT(*) AS N FROM #tmp b WHERE b.guidCol <= a.guidCol ) AS N
		FROM
			#tmp a
	) t2 ON t1.GuidCol = t2.GuidCol

ALTER TABLE #tmp DROP COLUMN guidcol;

SELECT * FROM #tmp;

DROP TABLE #tmp;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 16:10:50

Subquery might be an overkill in case of large datasets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arcticpro
Starting Member

6 Posts

Posted - 2012-09-28 : 20:16:02

Regarding maintaining going forward, this is being used for a mass import of employees into our existing application. The application handles incrementing the employeeID itself. Another column is used for the identity.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-29 : 08:00:02

As Visakh pointed out, if you have millions of rows in your table, you would want to stay as far away as you can from the query I posted earlier. But if you have only a few hundred or a few thousand rows in your table, you can try it (but first do it in a dev environment where we won't hurt the innocent).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources