Modify column type and replace data

Question

I have a table in SQL server 2008 R2 with 100 Million records.

One of the column is a foreign key to another table.

Table Student 
ID   Name    AccessToLap
1    Mike    1

Table AccessType
ID   Name
1    Allow
2    Deny

How can i change the column AccessToLab from int to varchar and change the values from 1 to Allow and from 2 to Deny without dropping and recreating the table.

Andomar · Accepted Answer · 2013-04-29 12:42:13Z

4

alter table Student add AccessType varchar(50);

update  s
set     AccessType = at.Name
from    Student s
join    AccessType at
on      at.ID = s.AccessToLap;

alter table Student drop column AccessToLap;

exec sp_rename 'Student.AccessType', 'AccessToLap', 'COLUMN'

edited Apr 29, 2013 at 12:42

answered Apr 29, 2013 at 12:34

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ian Kenney Over a year ago

and... EXEC sp_rename 'Student.AccessType', 'AccessToLap', 'COLUMN'

Maro Over a year ago

why are you renaming the column?

Andomar Over a year ago

The query adds a new column (AccessType) which is temporary. It updates the column to the right values using the old column (AccessToLap.) At the end, it drops the old column and renames the new column.

Collectives™ on Stack Overflow

Modify column type and replace data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related