question: REMOVING identity property in a TABLE? 1

[alfredjp]

could anyone show me a script on how to remove the IDENTITY property of a table just using ALTER TABLE and ALTER COLUMN?

ps: i do know how to put define a column to be an IDENTITY column, but i would like to do the reverse

thanks!

 

[psprague]

You can just turn it off, if that helps.

SET IDENTITY_INSERT mytable OFF

 

[alfredjp]

yeah... i have thought about that too...

unfortunately, if i wanted to do an insert on it, i need to put the ENTIRE column names after the

INSERT INTO

phrase instead of just using

INSERT INTO aTable SELECT * FROM anotherTable

 

[agoeddeke]

There is no easy way to do it (I asked this same question in a previous post). Here is some info from that post that helped me:

http://www.winnetmag.com/WindowsScripting/Article/ArticleID/22081/WindowsScripting_22081.html

I also found out that if you SELECT INTO and specify the identity column twice that it will remove the identity property from both. As in:

SELECT MyIdentity AS NewCol, * FROM MyTable INTO...

So using this you could simply run the SELECT INTO a table, drop the extra column and rename the table back.

Andy

 

[psprague]

Using '*' is not a particularly good programming practice for several reasons. SQL Server has to do the look up of columns itself, which consumes time and if, somewhere along the line, the table gets a column added, the code breaks.

 

[alfredjp]

to psprague (Programmer)

thanks for the advice

i guess id do just that - stop relying on

*