July 15, 2012 at 11:27 pm
Hi Friends,
I am new to sql server.
I have a table called Table1.
CREATE TABLE [dbo].Table1(
[ID] [int] NOT NULL,
[Name] [varchar](25) NULL,
) ON [PRIMARY]
I am entering both ID,Name values. I have almost 28965 rows. Now i want to enter only Name field value and ID should be Identity field and that should be starts from 28966. How can i change this? How to set the identity option to existing column? Please advice.
Thanks
Swetha.
July 15, 2012 at 11:51 pm
You can't change an existing column to identity, but you can have two options:
1.create a new table with identity column, then insert value from your old table(Table1)
2.create a column in existing table(Table1) with identity, update this field with data from old int column, then drop the old int column and rename new identity column with previous int column name.
🙂
July 15, 2012 at 11:55 pm
July 16, 2012 at 12:04 am
Hello,
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_Table1
(
ID int NOT NULL IDENTITY (28966, 1),
Name varchar(25) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (ID, Name)
SELECT ID, Name FROM dbo.Table1 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1 OFF
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
😉
Jeff.
July 16, 2012 at 12:06 am
Thank you all.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply