Adding a primary key column to an existing table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Adding a primary key column to an existing table?

Author

Topic

HowlingOdin
Starting Member

4 Posts

Posted - 2011-10-26 : 14:02:03

Hello,

Running into a fairly basic problem

For the sake of simplicity, let's take this existing table

Date State Temp
1-22 PA 86
1-23 TX 94
1-24 VA 88
. . .
. . .
. . .

What I'd like to do is to add an additional column called "P_ID" to use as the primary key. I want that key to incrementally increase by 1 for each row. If I create a new column, it will contain null values. I'd like to replace it with integers 1, 2, 3, 4, 5, etc. What is the best way in going about to do that? Everything I try with ROWNUMBER() or RANK() seems to set all of the P_ID values to 1, which isn't what I'm looking for

Expected result

Date State Temp P_ID
1-22 PA 86 1
1-23 TX 94 2
1-24 VA 88 3
. . . 4
. . .
. . .

any help is appreciated, thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 14:17:38

wouldn't State and Date be the natural key of the table?

Why do you want some erroneous number?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 14:23:18

..but if you insist



CREATE TABLE myTemps99 ([Date] datetime, [State] char(2), [Temperature] decimal(5,2))
GO

INSERT INTO myTemps99 ([Date], [State], [Temperature])
SELECT '1-22-11', 'PA', 86 UNION ALL
SELECT '1-23-11', 'TX', 94 UNION ALL
SELECT '1-24-11', 'VA', 88
GO

SELECT * FROM myTemps99
GO

ALTER TABLE myTemps99 ADD PK_Col int IDENTITY(1,1)
GO

SELECT * FROM myTemps99
GO

DROP TABLE  myTemps99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

HowlingOdin
Starting Member

4 Posts

Posted - 2011-10-26 : 14:26:36

quote:
Originally posted by X002548

wouldn't State and Date be the natural key of the table?

Why do you want some erroneous number?

Hello Brett,

This is just an example. This isn't the actual table. Just looking to find a way insert a new column in an already existing table which autoincrements by 1.

My actual table is much larger and has a lot of uncessary information, which why I didn't post it here

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 14:34:58

cut and paste the code and run it

Look at the ALTER

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

HowlingOdin
Starting Member

4 Posts

Posted - 2011-10-26 : 14:37:57

quote:
Originally posted by X002548

..but if you insist

CREATE TABLE myTemps99 ([Date] datetime, [State] char(2), [Temperature] decimal(5,2))
GO

INSERT INTO myTemps99 ([Date], [State], [Temperature])
SELECT '1-22-11', 'PA', 86 UNION ALL
SELECT '1-23-11', 'TX', 94 UNION ALL
SELECT '1-24-11', 'VA', 88
GO

SELECT * FROM myTemps99
GO

ALTER TABLE myTemps99 ADD PK_Col int IDENTITY(1,1)
GO

SELECT * FROM myTemps99
GO

DROP TABLE myTemps99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Thanks! Didn't realize it was so simple. I was doing all sorts of funky stuff which didn't work out

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-26 : 14:48:35

np op

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources