modify existing column in an table using SQL SERVER

Question

0.00/5 (No votes)

See more:

I Have 10 records in my table. i want to modify the table column with identity

like,

ALTER TABLE ALTER COLUMN INT IDENTITY (10, 1)
Already 10 Values are inserted in Table.

My Error:
Incorrect syntax near the keyword 'IDENTITY'.

Posted 25-Jul-12 21:15pm

oliver grace

Add a Solution

4 solutions

Solution 2

You forgot the column name - it should be between the words "COLUMN" and "INT"

Posted 25-Jul-12 21:28pm

OriginalGriff

Solution 3

Remove the old column and add new one.

SQL

--SQL Script:
ALTER TABLE Users DROP PK_Users
GO
ALTER TABLE Users DROP COLUMN UserID
GO
ALTER TABLE Users ADD UserID int NOT NULL IDENTITY(1,1) 
GO
ALTER TABLE Users ADD CONSTRAINT PK_Users PRIMARY KEY CLUSTERED ([UserID] ASC)
GO

Posted 25-Jul-12 22:14pm

graciax8

Comments

oliver grace 26-Jul-12 4:35am

Thank you...But my requirement is when i alter a table old column data also i need....is there anyother way.

graciax8 26-Jul-12 4:41am

see this.

http://dbaspot.com/sqlserver-programming/393269-how-add-identity-existing-column.html#post1447749 ^

Solution 4

In case if user wants identity also in varchar column for the above scenario..

SQL

drop table testtable2
go
CREATE TABLE TestTable2 (
 srlNo int NOT NULL IDENTITY(1, 1),
  Id varchar(100),
   Name varchar(50) NULL,
    Addres varchar(100) 
    ) ON [PRIMARY] 
    go 
    
   insert into TestTable2 values('test1','sant','test')
   go
    CREATE TABLE TempTable2 ( 
    Identifier int NOT NULL IDENTITY(1, 1),
     Id as 'test'+cast(Identifier as varchar(100)),
      Name varchar(50) NULL,
       Addres varchar(100)
       ) ON [PRIMARY] 
       go 
       SET IDENTITY_INSERT TempTable2 ON
        go 
        IF EXISTS ( SELECT * FROM TestTable2 ) 
        INSERT INTO TempTable2 (Identifier, Name,Addres ) 
        SELECT srlNo,Name, Addres FROM TestTable2 
        TABLOCKX 
        go 
        SET IDENTITY_INSERT TempTable2 OFF 
        go
        DROP TABLE TestTable2
         go
          Exec sp_rename 'TempTable2', 'TestTable2'
          
          select * from testtable2

Posted 27-Jul-12 0:18am

Santhosh Kumar Jayaraman

Updated 27-Jul-12 0:46am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-07-25T21:23:00

Solution 1

SQL

Alter Table Names
Add Id_new Int Identity(10, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

Please check.
http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column[^]

Posted 25-Jul-12 21:23pm

Santhosh Kumar Jayaraman

Comments

oliver grace 26-Jul-12 4:31am

Thank you for your reply ..in this approach i can't retain the existing data..
when i alter a table i want to have my existing data also...is there anyother way.....

Santhosh Kumar Jayaraman 26-Jul-12 4:33am

try this.

n this Approach you can retain the existing data values on the newly created identity column.

CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

oliver grace 26-Jul-12 4:45am

Where is the dbo.Names table are you already created this table

Santhosh Kumar Jayaraman 26-Jul-12 4:48am

Yes DBO.Names is the existing table. Here we are creating new table with identity column and insert existing records into new table and dropping the old table.

oliver grace 26-Jul-12 5:13am

Thank you Very mach... i got the answer

oliver grace 26-Jul-12 5:24am

my old table srlno is identity now i created ID id identity can i get both identity in a table...

Santhosh Kumar Jayaraman 26-Jul-12 5:26am

No Sqlserver allows only one identity column

oliver grace 26-Jul-12 5:47am

can we set varchar id to auto generic

Santhosh Kumar Jayaraman 26-Jul-12 5:52am

try like this.
CREATE TABLE Questions(
QuestionID int IDENTITY NOT NULL,
QuestionNo as 'Q'+Cast(QuestionID as Varchar(10)) PERSISTED PRIMARY KEY,
Question Varchar(200)
)

oliver grace 26-Jul-12 8:01am

same identity concept not working for id with varchar value

oliver grace 27-Jul-12 0:01am

using stored procedure can we do this concept in single table ?

Santhosh Kumar Jayaraman 27-Jul-12 0:02am

I didnt understand Your question

oliver grace 27-Jul-12 0:33am

existing table i have id varchar(100) how to set identity for id

oliver grace 27-Jul-12 4:28am

for integer id its working fine...when i use varchar for id getting problem.

Santhosh Kumar Jayaraman 27-Jul-12 4:31am

you are trying to do somethin like M01,M02 like this?

oliver grace 27-Jul-12 5:44am

yes

oliver grace 27-Jul-12 5:46am

but i cant get like that

Santhosh Kumar Jayaraman 27-Jul-12 5:50am

I told you right, you have to try something like this.
CREATE TABLE Questions
( QuestionID int IDENTITY NOT NULL,
QuestionNo as 'Q'+Cast(QuestionID as Varchar(10)) PERSISTED PRIMARY KEY, Question Varchar(200)
)

oliver grace 27-Jul-12 5:59am

I tried this but its not working..
CREATE TABLE TestTable2
(

srlNo int NOT NULL
IDENTITY(1, 1),
Id varchar(100),
Name varchar(50) NULL,
Addres varchar(100)
)
ON [PRIMARY]
go

CREATE TABLE TempTable2
(
Identifier int NOT NULL
IDENTITY(1, 1),
Id as 'test'+cast(Identifier as varchar(100)),

Name varchar(50) NULL,
Addres varchar(100)
)
ON [PRIMARY]
go

SET IDENTITY_INSERT TempTable2 ON
go

IF EXISTS ( SELECT *
FROM TestTable2 )
INSERT INTO TempTable2 ( Id, Name,Addres )
SELECT Id,
Name,
Addres
FROM TestTable2 TABLOCKX
go

SET IDENTITY_INSERT TempTable2 OFF
go

DROP TABLE TestTable2
go

Exec sp_rename 'TempTable2', 'TestTable2'

oliver grace 27-Jul-12 6:04am

Error like this:
-----------------
Msg 271, Level 16, State 1, Line 4
The column "Id" cannot be modified because it is either a computed column or is the result of a UNION operator.
Caution: Changing any part of an object name could break scripts and stored procedures.

Santhosh Kumar Jayaraman 27-Jul-12 6:08am

Can you raise it as a separate question? If someone wants to get asnwer for your question they might end up with different things.

oliver grace 27-Jul-12 6:14am

ok and once again thank you so much

Santhosh Kumar Jayaraman 27-Jul-12 6:19am

i have posted one more answer solution 4 check that

Santhosh Kumar Jayaraman 27-Jul-12 6:22am

Let me know if it works

pradiprenushe 27-Jul-12 7:18am

This was always problem for me. thanks for solution.my 5