Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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
Reply With Quote
  #8 (permalink)  
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
Reply With Quote
  #9 (permalink)  
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
Reply With Quote
  #10 (permalink)  
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


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:14 AM.


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