December 12, 2007 at 11:13 pm
How to alter a exist column which datatype is decimal(19,0)to identity
when i am using following query it is giving error
alter table ximport alter column autoid decimal(19,0) identity(1,1) not null
December 13, 2007 at 12:49 am
What error do you get?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2007 at 12:53 am
Hi,
I saved the change script after adding manualy the IDENTITY property to the autoid column in Enterprise Manager. This is the generated script:
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_ximport
(
autoid decimal(19, 0) NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_ximport ON
GO
IF EXISTS(SELECT * FROM dbo.ximport)
EXEC('INSERT INTO dbo.Tmp_ximport (autoid)
SELECT autoid FROM dbo.ximport TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_ximport OFF
GO
DROP TABLE dbo.ximport
GO
EXECUTE sp_rename N'dbo.Tmp_ximport', N'ximport', 'OBJECT'
GO
COMMIT
Regards,
Oana.
December 13, 2007 at 2:12 am
From Eneterprise Manager you can alter the table column as identity.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply