How to change a field's type?

Hi,
How could I change the field type through T-SQL?
I have tried Using the ALTER:
ALTER TABLE tblName ALTER COLUMN myID int
It didn't work...

And also, how could you rename a fieldname?
cheers,
Paul June A. DomagThe syntax you have listed is correct, although you may need to specify NULL/NOT NULL:
Alter Table dbo.SomeTable
Alter Column SomeField Varchar(101) NULL
For the rename funtion, use sp_rename you can find the syntax in books online...
Edit: If you are getting an error, please post the details of the error so we can dig a little deeper...
|||Hi,

I tried placing a NOT NULL statement and still it doesn't work...

ALTER TABLE myTable ALTER COLUMN myCol int NOT NULL

What is wrong with my statement?
BTW, the myCol field is numeric (I don't know if it matters)

ALSO, is there another alternative in renaming the field? Coz Im trying to achieve this in SQLServerCE and there is no sp_rename function in SQLServerCE...
cheers,
Paul June A. Domag|||Hey Paul,
I'm not familiar with SQL Server CE, so not sure if the T-SQL is any different.
In the absence of sp_rename, the following should work
-- Create the renamed column
Alter Table myTable with Check Add newColname varchar(100) not null
Go
-- copy the data to the new column
Update myTable set newColumn = oldColumn
Go
-- delete the old column
Alter Table myTable Drop Column oldColumn
Go
For the Alter statement that isn't working, can you please post the error message that you receive?
Thanks,
Tyler|||Hi,
Whew! Why didn't I think about that? tsk....
Thank you very much! You really made my day...

Here's the error message that I keep on getting in SQLServerCE:

FAILED: ALTER TABLE CustomerAddresses
ALTER COLUMN IDNew int
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 7
Param. 2: 0
Param. 3: IDNew
Param. 4:
Param. 5:

Cheers,
Paul June A. Domag|||Hi,
In addition to that, how could I modify a field to make it an IDENTITY column?
I tried:
ALTER TABLE mytable ALTER COLUMN IDNew IDENTITY

It generates an error saying that my field could not be made identity column after it was made...

BTW, the column that im trying to rename is an IDENTITY column, so by using the solution that you have provided, I need to restore the Autonumbering function on my field...
cheers,
Paul June A. Domag|||Hi Paul,
I think that Identity may be the issue. I suppose for an easy answer you could use the same method to change the column type, i.e., create a new column, copy the values and delete the orginal column.
To restore the numbering just do it when you declare the column:
Alter Table SomeTable
With Check Add
MyColumn INT IDENTITY (x, y) NOT NULL
Where x is the SEED value (i.e., what the next number will start at) and y is the INCREMENT (usually 1 to increment by one)
So if you where to declare the column as IDENTITY (1500, 1) the next record inserted would automatically have a value of 1500, etc.
Let me know if that makes sense.
Thanks!
Tyler|||Hi,
Sorry, but it didn't work. It seems that Identity columns couldn't be updated...
I used your steps:

ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
UPDATE t SET myNewCol = ID ' Error Here
ALTER TABLE t DROP COLUMN ID

BTW, my ID field (w/c is the identity column) is not entirely sequential. What I mean is, there have been some deletions and the numbering is somewhat jumbled. eg 1,2,3,8,11,13,21,22,25...

In this case I can't even use the new Identity Column that I would create coz the newly created identity would be sequential and thus would break my relationship with other tables...

Any other bright ideas?
cheers,
Paul June A. Domag|||Hi Paul
I think you cannot update an Identity column unless you set the identity insert on
ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
go
SET IDENTITY_INSERT t ON
UPDATE t SET myNewCol = ID ' Error Here
SET IDENTITY_INSERT t OFF
ALTER TABLE t DROP COLUMN ID
GO|||Hi,
Im currently using SQLServerCE and I doubt that this function/variable exists... Im trying to do this purely in SQL Statement...
Any Other Bright Ideas?
cheers,
Paul June A. Domag|||With a column set to Identity you cannot insert values unless your basically turn off the Identity feature, which is done as Eisa states.
If this doesn't work in SQL CE then we'll need to find a SQL CE Expert to answer this question.
Have you tried turning off the IDENTITY_INSERT? Also, make sure you insert GO statements after each line.
|||Hi,
Sad to say, it didn't work. I searched the BOL of SQLServerCE and the IDENTITY_INSERT switch isn't available. Guess, I'll have to find other means to solve this problem...

Thanks a lot Tyler Free and Eisa for your ideas and comments...
cheers,
Paul June A. Domag