How to modify a column in a SQL Server table with a script?
Tutorial to modify a SQL Server column with a T-SQL ALTER TABLE and ALTER COLUMN command. This SQL code allows you to modify the structure of a table. The table is created in the first part with a length of 20 characters. This already existing column is changed to a length of 100 characters.
The example used is the Sales table created in this CREATE TABLE SQL script example. Create the table first before changing the column. Connect to the target instance and database with SSMS and execute the code below in a new window.
-- Create the example table to store the Sales data CREATE TABLE [dbo].[Sales] ( [MonthName] NVARCHAR(20), [Amount] NUMERIC(5) );
T-SQL script to change the length of a column
In effect, use this example of SQL Server code to update the sales table and change the months column. The goal is to give it a length of 100 characters instead of 20 characters.
-- Script to increase the length of the NVARCHAR field from 20 to 100 characters ALTER TABLE [dbo].[Sales] ALTER COLUMN [MonthName] NVARCHAR(100);
Change the length of an SQL column without losing data
Modifying a table allows you to change the data stored in the table without deleting it. To modify a SQL Server table, the data already stored in the table must be compatible with the new data type. If the new data type is not compatible, then perform these actions in this order.
- Store the data in a temporary backup table
- Empty the source table with a truncate command
- Modify the table with the ALTER script above
- Insert the data back into the table with an INSERT INTO command
- Check the data in the column and in the table, e.g. count the number of rows
- Finally delete the backup table with a DROP TABLE request
This method allows you to change a SQL column and keep the data intact. Finally, here is an article that explains how to delete a SQL Server table from a database.
Finally, here is another tutorial to learn the syntax of SQL scripts to modify a table.