Click here to Skip to main content
12,242,812 members (46,738 online)
Rate this:
 
Please Sign up or sign in to vote.
hi

i get this Error
Incorrect syntax near 'int'

when i modify my table

ALTER TABLE upload  ALTER  COLUMN MODIFY id int IDENTITY(46,1)

help me .
Posted 28-Mar-13 1:08am
Comments
Jonathan [Darka] 28-Mar-13 6:10am
   
What have you tried so far?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Please this here for correct syntax of ALTER COLUMN statement.

Moreover, you cannot alter a column that is used as primary key ; you have to create a brand new table with correct columns, transfer data from old table to new one, delete old table and rename new one. The process could involve to suppress/recreate some foreign keys in other tables, also. Can be a tough task depending on the size of your database.

Hope it makes sense.
  Permalink  
Comments
Darsh_Basha 28-Mar-13 7:59am
   
Thx
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

--create test table

create table table1 (col1 int, col2 varchar(30))
 
insert into table1 values (100, 'olddata')
 

 
 --add identity column

alter table table1 add col3 int identity(1,1)
 
GO
 

 
--rename or remove old column

exec sp_rename 'table1.col1', 'oldcol1', 'column'
 
OR
 
alter table table1 drop column col1
 
--rename new column to old column name

exec sp_rename 'table1.col3', 'col1', 'column'
 
GO
 

 
--add new test record and review table

insert into table1 values ( 'newdata')
 
select * from table1
  Permalink  
Comments
Darsh_Basha 28-Mar-13 7:59am
   
Thx
Karruksen 5-Apr-13 0:28am
   
Pls Accept this answer..

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 28 Mar 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100