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
 General SQL Server Forums
 New to SQL Server Programming
 Adding a primary key column to an existing table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HowlingOdin
Starting Member

4 Posts

Posted - 10/26/2011 :  14:02:03  Show Profile  Reply with Quote
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!


Edited by - HowlingOdin on 10/26/2011 14:22:45

X002548
Not Just a Number

15586 Posts

Posted - 10/26/2011 :  14:17:38  Show Profile  Reply with Quote
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/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/26/2011 :  14:23:18  Show Profile  Reply with Quote
..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/


Go to Top of Page

HowlingOdin
Starting Member

4 Posts

Posted - 10/26/2011 :  14:26:36  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/26/2011 :  14:34:58  Show Profile  Reply with Quote
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/


Go to Top of Page

HowlingOdin
Starting Member

4 Posts

Posted - 10/26/2011 :  14:37:57  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/26/2011 :  14:48:35  Show Profile  Reply with Quote
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/


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.08 seconds. Powered By: Snitz Forums 2000