Alter the Table Columns without Dropping it

This blog explains the following,

• How to create the table
• How to alter the table (How add/modify Column/DataType in existing table)
  
• Ways to prevent Saving changes is not permitted in SQL Server

Step 1: Create table

use the following sql script to create Mas_Employee table.

Create table Mas_Employee(
   ID int primary key,
   Name varchar(50),
   Salary int,
   DeptID  varchar(10)
)

Step 2: Alter the table
For example, If you want to change the DataType of DeptId from Varchar(10) to int do the following steps:

1. Goto "Object Explorer" window.
2. Your Database.
3. Tables.
4. Right click on "Mas_Employee".
5. And select "Design".
6. Change the datatype from varchar(50) to int.
7. Save the table.

 At this point, you will get an error message as in the following.

 

Step 3: Ways to prevent Saving changes is not permitted in SQL Server

There are two ways to prevent the above error.

i ) Alter the table by using Sql Query.

ii) Disable "Prevent saving changes that require table re-creation" option.

 

Method 1: Alter table by using Sql Query

For example to add new column (Gender) in the existing table (Mas_Employee) use the below script

Alter table Mas_Employee
Alter column Gender varchar(40)

Method 2 : Disable "Prevent saving changes that require table re-creation" option to prevent the error.

• Goto Tools, select Options.
• Expand Designers, and select "Table and Database Designers".
• Then uncheck "Prevent saving changes that require table re-creation".
• Click OK.
  

 

I hope you enjoyed it.