{"id":18735,"date":"2022-06-30T13:45:33","date_gmt":"2022-06-30T11:45:33","guid":{"rendered":"https:\/\/expert-only.com\/?p=18735"},"modified":"2022-11-29T11:36:24","modified_gmt":"2022-11-29T10:36:24","slug":"modify-a-sql-server-column-with-a-script","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/modify-a-sql-server-column-with-a-script\/","title":{"rendered":"Modify a SQL Server column with a script"},"content":{"rendered":"\n

How to modify a column in a SQL Server table with a script?<\/em><\/strong><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/a> with SSMS and execute the code below in a new window.<\/p>\n\n\n\n

-- Create the example table to store the Sales data\nCREATE TABLE [dbo].[Sales] (\n  [MonthName] NVARCHAR(20),\n  [Amount] NUMERIC(5)\n);\n<\/pre>\n\n\n\n
T-SQL script to change the length of a column<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
-- Script to increase the length of the NVARCHAR field from 20 to 100 characters\nALTER TABLE [dbo].[Sales]\n  ALTER COLUMN [MonthName] NVARCHAR(100);\n<\/pre>\n\n\n\n
Change the length of an SQL column without losing data<\/h2>\n\n\n\n
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<\/em><\/strong> with the new data type<\/a>. If the new data type is not compatible, then perform these actions in this order.<\/p>\n\n\n\n
    \n
  1. Store the data in a temporary backup table<\/a><\/li>\n\n\n\n
  2. Empty the source table<\/a> with a truncate command<\/li>\n\n\n\n
  3. Modify the table with the ALTER script above<\/li>\n\n\n\n
  4. Insert the data back into the table with an INSERT INTO command<\/a><\/li>\n\n\n\n
  5. Check the data in the column and in the table, e.g. count the number of rows<\/li>\n\n\n\n
  6. Finally delete the backup table<\/a> with a DROP TABLE request<\/li>\n<\/ol>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n
    How to drop a SQL Server table with a script?<\/a><\/blockquote>