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.
| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2008-04-23 : 14:41:50
|
| Hi All,I have table called Product with seven columns all of nvarchar type. There are 150,000 records in this table. Also there's no unique identifier column in this table. I want to put a column lets say Column1 which start from 1 to 150000. The Column1 should be a unique column.How can achive this in SQL Server 2005?Looking for a quick help.Thanks a million,Zee |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-23 : 14:43:46
|
| [code]SELECT ROW_NUMBER() OVER (ORDER BY YourtablemainColumn) AS RowNo,*FROM YourTable[/code] |
 |
|
|
bfoster
Starting Member
30 Posts |
Posted - 2008-04-23 : 14:46:08
|
| I think this should work.ALTER TABLE Product ADD Column1 INT IDENTITY(1,1) NOT NULL |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-23 : 14:57:49
|
quote:
Originally posted by bfoster
I think this should work.ALTER TABLE Product ADD Column1 INT IDENTITY(1,1) NOT NULL
Ok column is created. now how will it get the autonumbered values for existing records? |
|
|
|
bfoster
Starting Member
30 Posts |
Posted - 2008-04-23 : 15:10:36
|
| Did you query the table after adding the column because when I run the SQL to add the column it does the numbering at that time?CREATE TABLE Product(Column2 VARCHAR(5), Column3 VARCHAR(5), Column4 VARCHAR(5))INSERT INTO Product SELECT 'a', 'b', 'c'INSERT INTO Product SELECT 'b', 'c', 'd'INSERT INTO Product SELECT 'c', 'd', 'e'INSERT INTO Product SELECT 'd', 'e', 'f'INSERT INTO Product SELECT 'e', 'f', 'g'ALTER TABLE Product ADD Column1 INT IDENTITY(1,1) NOT NULLSELECT * FROM Product |
|
|
|
|
|
|
|
|
How to insert a numeric identity column?