Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 926
  • Last Modified:

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

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
DeMyu
Asked:
DeMyu
  • 2
1 Solution
 
illCommented:
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
 
DeMyuAuthor Commented:
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
 
illCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now