Hi,

Does anyone know how to remove the IDENTITY off a column that is set as IDENTITY.

I can do that from the Enterprise manager, by setting the IDENTITY to NO in the Column Properties window. But I am looking for the TSQL script that could do this. If someone has done this successfully, please let me know here.

Thanks,


_________________________
- Vijay G
Strive for Perfection

__________________
- Vijay G

Hi Vijay,

If you look at the way Management Studio does it under the covers, that is probably the only way to do it.

Try this:

Right mouse click on the table in Management Studio and select Modify. In the properties window of the table, set the Identity Property to NO.

THEN...

Click on the very left button on the toolbar called Generate Change Script (it is the botton to the left of the Set Primary Key button).

This will display the T-SQL script that Management Studio will use to make the change under the covers. You will notice that Management Studio creates a temporary table, copies the data from the original table to the temporary table, then renames the temporary table.

If you have any PK/FK relationships then those will also be scripted as well.

So, i think the only way to do this in T-SQL is to manually do it like that.

Hope this helps...



Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

If you only need to do it as a temporary measure, you could do the following just before the insert...

SET IDENTITY_INSERT owner.table ON

Don't forget to do the fillowing right after the insert

SET IDENTITY_INSERT owner.table OFF


--Jeff Moden

Thanks for the replies. Just trying to get myself active in this place here after a long layoff...

The requirement is to archive data on a regular basis which needs to go in as scripts and no manual interventions to have this done. And it is not mandatory to have relationships/Identity and stuff in the archive DB. The problem is using SELECT INTO just clones the table to the archive DB and it has IDENTITY column too. So the next available option is to Check for the Table existence and Create it one, then use INSERT INTO. I already tried Setting IDENTITY_INSERT ON and OFF for this job, but some how the data doesn't go in for some reasons. This is to happen for multiple tables based on different criteria.

I never wanted to use CREATE TABLE statements into the Archive SP, thought there should be some trick with ALTER TABLE ... ALTER COLUMN syntax, but the effort was in vain. When this is allowed from the UI why not a script be available for doing the same was my question. Still I thought posting it here could get me some look in. But it seems other than setting IDENTITY_INSERT on/off there is not work around for this.

As SCOTT said, I even thought of scripting what it internally does, but that is too much of effort for this, than straight away creating the table if it doesn't exist in the Archive DB.

Thanks for your inputs.
Cheers

_________________________
- Vijay G
Strive for Perfection

I went through an article from microsoft site, that confirmed that setting IDENTITY_INSERT ON/OFF was having a bug in it and it had a fix for it too...

_________________________
- Vijay G
Strive for Perfection

Excellent. Does this solve your problem?

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html

Why didn't you say that in the first place? :D

If you CAST the identity column as an INT in the SELECT INTO, I believe that will prevent the column from being created as an Identity column... haven't had to do that so I haven't tried it.

--Jeff Moden

>>...how to remove the IDENTITY off a column ..

  I'm not really answering your question, but here's something to consider if you haven't already:
1. In your select statement, specify the column names, omitting the id column. Omitting the id column will allow you to

  'select xxx,xxx,... t into new table
   where the resulting newly created table will not have the id column, since you did not specify one.


2. Or to insert new rows into existing table, where the destination table or the source table has an id column,
    Insert into TblDest (columnname1, columnname2)
            (select columnname1, columnname2 from TblSource)

     where columnname1, columnname2, etc. are non id columns in your tables.






Thanks,
J K

JK,

This wont solve my purpose, I wanted to remove the IDENTITY property as doing a SELECT INTO clones the destination table as that of the source table, and my intention was to not create the table separately using a create statement. But your answer is such that I wanted to remove the identity column and insert the others.

Anyways thanks for your effort and reply.

cheers

_________________________
- Vijay G
Strive for Perfection

try this....

sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
where id = object_id('your table name')
and name = 'column name'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go


insert into table select * from table2

go

sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat + 1 /*turn on bit 1 which indicates it's an identity column */
where id = object_id('yourtablename')
and name = 'column name'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go