Microsoft SQL Compact Edition rename column

Question

Hy,

I'm new with SQL Compact Edition and I am having problems renaming a column. I know that you can rename a table using sp_rename and this doesn't work with columns.

I've searched for an alternative, but didn't find one.

Can I delete a column and then add a new one after a specific column? If I delete the column and add it after the a specified one the data would be lost right?

Is this another issue of SQLCE that once you have created the table it can't be properly modified?

Thanks in advance

Answer 1

It does indeed seem that SQL CE wont allow changing column names.

You're on the right track with creating a new column and deleting the old.

If you just add a column and delete the old you will loose the data so you need to issue an update statement to shift the data from the old to the new.

Something along the lines of

alter Table [dbo].[yourTable] add [newColumn]

update yourTable set newColumn = oldColumn

alter Table [dbo].[yourTable] drop column [oldColumn]

Should create your new column, duplicate the data from old to new and then remove the old column.

Hope it helps!