Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 23rd, 2006, 07:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default Removing off the Identity Property

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
 
Old November 23rd, 2006, 10:53 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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
 
Old November 23rd, 2006, 11:01 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 23rd, 2006, 11:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old November 24th, 2006, 02:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old November 24th, 2006, 10:31 AM
SQLScott's Avatar
Wrox Author
 
Join Date: Dec 2004
Location: Wellington, FL , USA.
Posts: 338
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Excellent. Does this solve your problem?

Scott Klein
Author - Professional SQL Server 2005 XML
http://www.wrox.com/WileyCDA/WroxTit...764597922.html
 
Old November 24th, 2006, 10:40 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 25th, 2006, 01:04 PM
Authorized User
 
Join Date: Dec 2004
Location: Bakersfield, CA, USA.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

>>...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
 
Old November 27th, 2006, 06:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by jking
 >>...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
 
Old January 29th, 2007, 02:52 PM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
dropping the identity property of column sandeep SQL Server 2005 3 March 2nd, 2007 01:57 PM
Identity Property issue Navy1991 SQL Server 2000 2 January 6th, 2006 05:27 AM
What is @@identity property? subhashbankey SQL Server 2000 2 December 22nd, 2005 09:01 PM
Identity soccers_guy10 SQL Server 2000 3 September 2nd, 2003 07:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.