How to Alter Tables and Modify Columns in SQL Server
In this tutorial we will show how to use the ALTER TABLE statement to modify columns in a table. We can either add, delete, or modify columns in an existing table. This is especially useful when working with large databases, making possible lengthy tasks quick and precise.
Setting Up
Before we alter a table we must first create one and insert values into it. We will create a table called Persons with columns of P_Id, LastName, FirstName, Address, and City.
Next we will insert entries into the table with the INSERT statement.
Add a Column to a Table
We will add a column called ‘DateOfBirth’ into the table. We will use the Add statement and add a desired column name along with a data type.
Change a Column Data Type in a Table
We will change the data type of DateOfBirth from datetime to nvarchar. We will use the ALTER COLUMN statement to change the data type of DateOfBirth.
If you refresh the object explorer, you will see that the data type of DateOfBirth is now nvarchar(30):
Finally, we will drop the DateOfBirth column we added earlier. The syntax is similar to previous example, but instead uses the DROP statement to let SQL Server know that the specified column is to be dropped from the table.
By using of the DROP statement, we are able to drop the DateOfBirth column.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
AlterTableStatement.zip
Setting Up
Before we alter a table we must first create one and insert values into it. We will create a table called Persons with columns of P_Id, LastName, FirstName, Address, and City.
Code Block
PersonsTable.sqlce
Create the Persons table.CREATE TABLE Persons
(
P_Id int,
LastName nvarchar(30),
FirstName nvarchar(30),
Address nvarchar(50),
City nvarchar(30)
)
Code Block
PersonsValues.sqlce
Insert people into the Persons table.
INSERT INTO Persons (P_Id, LastName, FirstName, Address, City)
SELECT 1, 'Jones', 'Mich', '1 Street', 'New York'
UNION ALL
SELECT 1, 'Jones', 'Mich', '1 Street', 'New York'
UNION ALL
SELECT 2, 'Henderson', 'Paul', '2 Street', 'Miami'
UNION ALL
SELECT 3, 'Harriss', 'Tom', '3 Street', 'Miami'
UNION ALL
SELECT 4, 'Gonzalez', 'Anthony', '4 Street', 'Orlando'
UNION ALL
SELECT 5, 'Harriss', 'Tom', '5 Street', 'Miami'
UNION ALL
SELECT 6, 'Smith', 'Scoht', '6 Street', 'Los Angeles'
We will add a column called ‘DateOfBirth’ into the table. We will use the Add statement and add a desired column name along with a data type.
Code Block
AddComlun.sqlce
Add a DateOfBirth column.ALTER TABLE Persons
Add DateOfBirth datetime
I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.
If you refresh the object explorer, you will notice the new DateOfBirth column in the Persons table or simply run a query to see the changes. Also notice how DateOfBirth is of datetime data type, we will change it in the next example.Change a Column Data Type in a Table
We will change the data type of DateOfBirth from datetime to nvarchar. We will use the ALTER COLUMN statement to change the data type of DateOfBirth.
Code Block
AlterColumnType.sqlce
Change the DateOfBirth data typeALTER TABLE Persons
ALTER COLUMN DateOfBirth nvarchar(30)
We used over 10 web hosting companies before we found Server Intellect. Our new server with cloud hosting,was set up in less than 24 hours. We were able to confirm our order over the phone. They responded to our inquiries within an hour. Server Intellect’s customer support and assistance are the best we’ve ever experienced.
Drop a Column in a TableFinally, we will drop the DateOfBirth column we added earlier. The syntax is similar to previous example, but instead uses the DROP statement to let SQL Server know that the specified column is to be dropped from the table.
Code Block
DropColumn.sqlce
Code Block DescriptionALTER TABLE Persons
DROP COLUMN DateOfBirth
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
As you can see, the table looks as if we did not add a DateOfBirth column. The ALTER TABLE statement helps with mistakes made in the table or extra columns that need to be added.Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
AlterTableStatement.zip
