Can i drop identity type from a column???
-
is it possible to drop an identity type form a column without either dropping /rebuilding the table (as done in EM and MS) or drop / rebuild the column?
is there just a simple alter statement that will drop this type from the column so i can assign a new column to have identity......?
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
-
If the column is Primary Key and other tables references it, no.
otherwise
ALTER TABLE Table1 DROP COLUMN
N 56°04'39.16"
E 12°55'05.25" -
thanks for that peter but i don't actually want to drop the column itself, just the idenity from it.
so just to clarify, i want to keep this column exactly as it is EXCEPT i do not want it to have identity insert on it as i want to add a new column to the table which will have an identity insert on it....
i know i can drop the column or in fact the table and rebuild but was wondering if it were possible to drop the identity insert without doing that....???
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
-
Unfortunately you cannot drop the identity property with a simple alter table statement. You will need to rebuild this table (create another one with no identity property, copy the data, and move the table).
You could actually use Management Studio, expand in the object explorer the table, and rightclick on the column (and set the Identity property to No). Not sure how well Management Studio does the rebuild, but in a simple testcase for me it preserved the data. Do make a backup
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
thanks for the confirmation Andras! i've tried it through MS (ideally going to be running it through a stored proc) and checked the change script and it basically creates a temp table with the new column set as identity, bulk inserts all the data from current table to the temp, drops current table and renames the temp to what the current table was.
Just seems like a lot of work for what i thought would be a simple change but to be honest it works relatively quickly as the table is not too large....
here is a cut down version of the script (uid is the new column i'm adding):
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
GO
CREATE TABLE dbo.Tmp_MyTable
(
uid int NOT NULL IDENTITY (1, 1),
PrtNo nvarchar(20) NULL,
PrtDesc nvarchar(70) NULL,
etc
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_MyTable OFF
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
EXEC('INSERT INTO dbo.Tmp_MyTable (PrtNo, PrtDesc, etc)
SELECT PrtNo, PrtDesc, etc FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'
GO
CREATE INDEXES.............etc
GO
COMMIT
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply