Removing IDENTITY property in SQL Server

HI,

SET IDENTITY_INSERT TABLENAME OFF

REGARDS

sriram

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Hi
Yes. We can do this by the following two way...

A) Removing IDENTITY property by MANAGEMENT STUDIO :
1. Right click on Table : "tbl_expenses"
2. Click "Modify" Menu Item
3. Expand the "Identity Specification" Property on "Column Properties" Property pag.
4. Chane the property "(Is Identity)" YES to NO.

B) Removing IDENTITY property by Scripts :
1. Create a backup table :tbl_expenses_Backup with same structure of tbl_expenses WITHOUT IDENTITY property.
2. Copy the actual data into backup table : tbl_expenses to tbl_expenses_Backup.
3. Drop the actual table : tbl_expenses
4. Rename the backup table tbl_expenses_Backup to tbl_expenses.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Pandian

I try and come back to you incase of any clarifications. thanks

regards
sarvesh s

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

Hi-

Open the table in design Mode.
Select the column Name
From the property list select the Identity Specification and set IsIdentity to No.

Thanks & Regards
Lakhan Pal Garg
http://lakhangarg.blogspot.com

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

The best possible option would be to use T-SQL Command.

SET IDENTITY_INSERT tbl_expenses Off

Hope this helps

Cheers !
Goldy

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator

--If other tables' foreign keys point to the IDENTITY column, drop them. Here, the script drops
--the foreign key pointing to Orders2.OrderID from OrderDetails2.
ALTER TABLE OrderDetails2
DROP CONSTRAINT FK_OrderDetails2_Orders2

--If a primary key exists on the IDENTITY column, drop the primary key constraint the way this
--script drops the primary key from Orders2.
ALTER TABLE Orders2
DROP Constraint PK_Orders2

--Add another column with the same data type as the IDENTITY column to Orders 2 and allow
--NULLs.
ALTER TABLE Orders2
ADD new_OrderID int NULL

--Update the new column with the values of the IDENTITY column.
UPDATE Orders2
SET new_OrderID = OrderID

--If the new column doesn't permit NULLs, alter the column to NOT NULL.
ALTER TABLE Orders2
ALTER COLUMN new_OrderID int NOT NULL

--Drop the IDENTITY column.
ALTER TABLE Orders2
DROP COLUMN OrderID

--Rename the new column to the dropped IDENTITY column's name.
EXEC sp_rename 'Orders2.new_OrderID', 'OrderID', 'COLUMN'

--If a primary key exists on the new column, recreate the key. In this case you recreate the
--primary key on Orders2.
ALTER TABLE Orders2
ADD CONSTRAINT PK_Orders2 PRIMARY KEY(OrderID)

--On other tables, recreate any foreign keys that originally pointed to the old IDENTITY column
--and point them to the new column. Here, you recreate the foreign key on OrderDetails2.
ALTER TABLE OrderDetails2 WITH NOCHECK
ADD CONSTRAINT FK_OrderDetails2_Orders2
FOREIGN KEY(OrderID)
REFERENCES Orders2(OrderID)

more info
------
http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

Sarvesh, if this helps please login to Mark As Answer. | Alert Moderator