How to Remove Identity Property
-
I need to remove the identity feature of a table and then put it back, in t-sql, without dropping the column. This should be easy to do, but BOL doesn't have this exact scenario
Any ideas?
tia
Neil.
-
You can remove the Identity property of a column using SQL EM.
You can right click on a table and click Design Table. Point on the column on the table and select No from the Column properties down the page.
Hope this helps.
--Kishore
However
-
Prior to issuing your INSERT statement, issue:
SET IDENTITY_INSERT <table_name> OFF
after your INSERT statement issue:
SET IDENTITY_INSERT <table_name> OFF
This only removes it for the duration of your session (I think).
It can't be done permanently. What you will find if you try Kishore's suggestion (above) is that it will create a new identical table (save for the identity) containing the same data, drop the original table and rename the new table to what the original table was called. EM will script out the change for you so you can get hold of the T-SQL that does this.
e.g.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_jamietest
(
id int NOT NULL,
str varchar(10) NULL
 
ON [PRIMARY]GO
IF EXISTS(SELECT * FROM dbo.jamietest)
EXEC('INSERT INTO dbo.Tmp_jamietest (id, str)
SELECT id, str FROM dbo.jamietest TABLOCKX')
GO
DROP TABLE dbo.jamietest
GO
EXECUTE sp_rename N'dbo.Tmp_jamietest', N'jamietest', 'OBJECT'
GO
COMMIT
Hope that helps.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Thanks for the help, but I'm afraid both do not help me in this case.
As mentioned, I need to do this in T-SQL not EM.
The set identity_insert on doesn't work for me either unfortunately, as I am wanting to do this for 9 tables in one script before loading the data from Access queries then re-enabling the identity fields, which makes switching it on then off per table difficult.
I was hoping for t-sql to remove the identity feature of the field, like alter table X drop constraint Y.
I guess the identity_insert is the only way, so I'll have to do it manually.
thanks anyway.
Neil.
-
I didn't put the smiley in there by the way!!! All I did was copy and paste!!!
Whoops!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Don't forget...EM will give you the T-SQL that will do this for you. If you dno't mind dropping table...it'll work.
If you want to request to be able to remove an identity (which I think would be a good idea) send an email to sqlwish@microsoft.com
Are identities considered to be constraints? I guess they're not because they're not in sysconstraints which would explain why you can't get rid of the identity using ALTER TABLE <table_name> ALTER COLUMN <column_name>
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Niel,
There is no such simple T-SQL to remove the IDENTITY property as you would remove a constraint. Even Enterprise Manager has to do a little song and dance to do it... it makes a copy of the of the source table with mods, drops the source table, and renames the new table the same as the original source table. The easiest way to write the T-SQL to do it is to NOT write it... start doing the process in Enterprise Manager and save the T-SQL it generates. It's safe and won't make the same mistakes that most people would make in designing such a task...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply