[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How can I disable and re-enable the IDENTITY property on a table column using T-SQL.

Posted on 2004-08-30
3
Medium Priority
?
924 Views
Last Modified: 2008-01-09
I would like to be able to disable and re-enable the identity property on a column using T-SQL. Here is my sample table:

CREATE TABLE [dbo].[tblTestIDENT] (
      [Col1] [int] IDENTITY (1, 1) NOT NULL ,
      [Col2] [varchar] (50) NULL
) ON [PRIMARY]
GO

What effect would this have if I have say 100 million records in the table?
0
Comment
Question by:DeMyu
  • 2
3 Comments
 
LVL 12

Expert Comment

by:ill
ID: 11931355
SET IDENTITY_INSERT [tblTestIDENT] ON
insert into tblTestIDENT (col1, col2) values (10, 'test1')
insert into tblTestIDENT (col1, col2) values (1001, 'test2')
SET IDENTITY_INSERT [tblTestIDENT] OFF
insert into tblTestIDENT (col2) values ( 'test3')
select * from [tblTestIDENT]
0
 

Author Comment

by:DeMyu
ID: 11931847
Thank you for your reply. This does not actually remove the identity property of the column in Enterprise Manager. Is there a way to do this in T-SQL such that when I go into EM I wouldn't see the property on the column. I have to do this on a table with about 70 million rows.

Thanks
0
 
LVL 12

Accepted Solution

by:
ill earned 1500 total points
ID: 11932089
i'm afraid, you need to recreate table. here is a useful link for you:
http://www.examnotes.net/archive79-2002-7-47264.html
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question