Can I add a integer field with identity and identity seed=1, increcement=1?

Can I add a integer field with identity and identity seed=1, increcement=1?

Post by Quentin Hu » Sun, 05 Sep 2004 01:10:39


Hi:

I have a table without primary key. Now I want to modify it, I want to add
an key field typed integer with identity and identity seed=1, increcement=1.
I tried, but failed.

Can I do that? If not, any suggestion?

Thanks

Q.
 
 
 

Can I add a integer field with identity and identity seed=1, increcement=1?

Post by Aaron [SQL » Sun, 05 Sep 2004 01:15:35

> I tried, but failed.

What does "tried" mean? What was the table structure, what data was in the
table, what statement did you run, and where did you run it? What does
"failed" mean? Did you get an error message? If so, what was it?

I called the doctor this morning, and I told him that I tried to get out of
bed, but failed. What do you think his response was?

Anyway here is a repro that, I think, might do something along the lines of
what I think you could be trying to do. But since you really didn't include
any specific information, this is the best I can do, and tell you that, yes,
you can add an integer, make it identity, and make it a primary key... as
long as you do it all at once.


CREATE TABLE mort
(
dt SMALLDATETIME
)
GO

INSERT mort SELECT GETDATE()
INSERT mort SELECT GETDATE()-5
GO

ALTER TABLE mort ADD MortID INT IDENTITY(1,1) PRIMARY KEY
GO

SELECT * FROM mort
GO

DROP TABLE mort
GO

--
http://www.yqcomputer.com/
(Reverse address to reply.)

 
 
 

Can I add a integer field with identity and identity seed=1, increcement=1?

Post by Roji. P. T » Sun, 05 Sep 2004 01:23:57

Yep

ALTER TABLE Titles
ADD id int IDENTITY(1,1)

Select * from titles

--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com




increcement=1.
 
 
 

Can I add a integer field with identity and identity seed=1, increcement=1?

Post by Quentin Hu » Sun, 05 Sep 2004 03:01:56

Sorry, my problem is:

there is a column named "adddate" which is assigned when add a new record.
When I add a new column named "id", the orders of them are different. For
example, if sort by id, a record added earlier may be showed after a record
added later on. If sort by adddate, the record which id=5 may be showed up
behind the record which id=50.

Can I fix the problem?

Thanks

Q.
 
 
 

Can I add a integer field with identity and identity seed=1, increcement=1?

Post by Roji. P. T » Sun, 05 Sep 2004 03:09:04

OK. Probably you can do it the EM does it.
Thats like

1. Rename the table (to temp_table)
2. Create a new table with the original table name, also with the identity
column
3. copy data from the old table to the new table (This doesnt guarantee the
order even if you specify the order by clause. But most of the time it
does)
4. drop the temp table.


--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com




record
 
 
 

Can I add a integer field with identity and identity seed=1, increcement=1?

Post by Aaron [SQL » Sun, 05 Sep 2004 03:15:52

? If you can identify the order by datetime, why do you need a rank column?
Seems like redundant information to me. If you want to show row numbers
when you query the data, do so in the SELECT, not in the storage.

--
http://www.yqcomputer.com/
(Reverse address to reply.)








record