query to change the column type

[TMRO]

hi all,

i need to write a query to change a column type from int to int identity (1,1)

How do I do that?

Thx

 

[jbenson001]

look up alter table in books on line.

 

[bborissov]

Alter table doesn't help here, you can't use ALTER TABLE to make a field identity.
If you want to see what EM does when you change this better script whole action and look in generated script, it creates a temporary table, add identity field, add all constraints, all keys and indexes, triggers etc. Insert all records from old table and then rename temp table to old one.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jbenson001]

WOW, didn't know you couldn't do that.. good to know..
Thanks George.. I will check the resulting script from this

 

[gmmastros]

Ahem... thanks Boris.

It was good advice, but not mine.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

Whoops.. yeah.. thanks Boris.. sorry about that.. it's Monday ..

 

[Catadmin]

Or you could just add a new column as type identity, turn Identity Insert On and update the table so the New column values = the Old column values, and then drop the old column if you don't need to keep the values in the old column.

If you want to be complicated about it. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"