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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-21 : 09:14:29
|
| Doug writes "Is there a simple way to remove the IDENTITY property from a column in a table?I am writing a stored procedure to create a copy of a table which will then be used to record all amendments to the first table.I use 'SELECT INTO' to create the table (with a FALSE WHERE clause), and then use 'ALTER TABLE ALTER COLUMN' to add extra columns into which I will add historical audit data, like 'username', 'workstation, 'program' etc..The problem is that 'SELECT INTO' copies the IDENITY property to the new table. I don't want this, because when I later INSERT into the history table, I use 'INSERT..SELECT', and you can't do this with a table that has an IDENTITY column, even with 'SET IDENTITY_INSERT ON'.I have tried to look at ways of removing the IDENTITY property from the column after the table has been created, but the only way to do this appears to be to copy the table to a temporary table and then load the data and rename back. The problem with this is that if the table has structure, like a primary key, the structure has to be re-created as well. The EM 'Design table' dialogue appears to have all the logic to do this, but I don't really want to make my SP that complicated.I am using Windows 2000 Professional SP2, SQL Server Personal Edition 8.00.194.Can anyone help please?" |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-05-21 : 09:20:38
|
| Why can't you use INSERT...SELECT on a table with an identity column? Should work just fine as long as you don't include the identity column in your insert or select lists.Jeff BanschbachConsultant, MCDBA |
 |
|
|
|
|
|
|
|
Removing IDENTITY property from column