creating ID for existing records

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

SQL Server Development (2000)

creating ID for existing records

Author

Topic

JD Waters
Starting Member

1 Post

Posted - 2004-06-30 : 15:15:11

Hello,

I've inherited a ASP/SQL solution and I need to 'fix' it.

A Table has been setup with no Primary Key field. The Table contains approx 140 records.

What I'd like to do is
1) establish ID as a primary key field (a number from 1 to 1000)
2) loop through the existing records and insert a unique value into the ID field

I think I can handle future entries into the DB. I'm just not sure how to do the above.
Can anyone help?

JD

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-30 : 15:46:09

Cut and paste this in to QA for an example...


USE Northwind
GO

CREATE TABLE myTable99(Col1 real, Col2 char(1) DEFAULT 'Y', Col3 datetime DEFAULT GetDate())
GO
SET NOCOUNT ON
DECLARE @x int
SELECT @x = 0
WHILE @x < 140
  BEGIN
	INSERT INTO myTable99(Col1, Col2) 
	SELECT CONVERT(real,RAND()), CASE WHEN CONVERT(real,RAND()) < .5 Then 'Y' ELSE 'N' END
	SELECT @x = @x + 1
  END

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col4 int IDENTITY(1,1) NOT NULL PRIMARY KEY
GO

SELECT * FROM myTable99
SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources