SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update rows with increasing number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arcticpro
Starting Member

6 Posts

Posted - 09/27/2012 :  19:51:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 09/27/2012 :  22:54:37  Show Profile  Reply with Quote
on what order you want to insert number? on alphabetical order of employee names?

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

Go to Top of Page

arcticpro
Starting Member

6 Posts

Posted - 09/28/2012 :  12:02:24  Show Profile  Reply with Quote
No particular order is needed. They just need a unique # assigned. If you need an order, then by "LastName" is fine. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2012 :  12:38:28  Show Profile  Reply with Quote
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/

Go to Top of Page

arcticpro
Starting Member

6 Posts

Posted - 09/28/2012 :  12:52:56  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2012 :  13:43:15  Show Profile  Reply with Quote
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/

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/28/2012 :  14:42:36  Show Profile  Reply with Quote
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;

Edited by - sunitabeck on 09/28/2012 14:43:25
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/28/2012 :  16:10:50  Show Profile  Reply with Quote
Subquery might be an overkill in case of large datasets

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

Go to Top of Page

arcticpro
Starting Member

6 Posts

Posted - 09/28/2012 :  20:16:02  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/29/2012 :  08:00:02  Show Profile  Reply with Quote
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).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000