SQL How to Add a Column : 4 ways with examples

databases icon representing SQL how to add a column

Contents

In this post on SQL How to add a column, we will look at 4 different approaches to adding columns. Tables and Columns are the fundamental building blocks of any relational database. Adding a column to a database table is relatively easy if you are familiar with DDL commands.

DDL stands for Data Definition Language and is associated with defining objects in a database. Depending on the database vendor, the commands considered DDL can vary slightly. However, CREATE, ALTER, DROP are considered DDL universally. The syntax used in these examples are Oracle specific. However, you should be able to use the same in any other database with minimum tweaks. 

1. SQL How to add a Column when creating a table

The best way to add column(s) is when creating a table. using the CREATE DDL. This would require you to plan your data model well ahead of time. However, we all know that never works. 

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar(30) default 'John',
last_name        varchar(30) default 'Doe',
email            varchar(60) default 'john.doe@xyz.com',
phone            varchar(15) default '000-000-0000',
hire_date        date        default '1901-01-01',
sales_id         integer     default 0
);

2. SQL How to add a Column using DROP and Re-CREATE

In some cases, you do not really care about the data in your table. For instance if you are working in a development environment and have dummy data or scrambled data. If this defines your situation, then the best option is to just copy the DDL for the existing table, drop the table and then recreate it with the new fields you need. The steps to follow this approach is listed below.

STEP 1: Copy the table DDL into a text editor

STEP 2: Drop the table

DROP TABLE employees;

STEP 3: Recreate the table with the new columns. In this case we add MIDDLE_NAME, SUFFIX and DOB to the new DDL.

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar(30) default 'John',
middle_name     varchar(30),
last_name        varchar(30) default 'Doe',
suffix     varchar(10),
email            varchar(60) default 'john.doe@xyz.com',
phone            varchar(15) default '000-000-0000',
dob     date,
hire_date        date        default '1901-01-01',
sales_id         integer     default 0
);

3. SQL How to add a Column using ALTER TABLE Command

Now let’s look at a third option for adding a column using ALTER TABLE.

If you have data in your table and do not want to loose it, or any of the constraints and permissions, then an ALTER TABLE command is the best. You can add a single column or multiple columns with constraints and data type to a single table using this statement. However, do keep in mind that an ALTER TABLE adds the column to the end of the table as the last column. 

Using the EMPLOYEES tables from the previous example, lets add MIDDLE_NAME to this table.

OPTION1 : Adding a single column with constraint and data type to a table.

ALTER TABLE employees 
ADD middle_name varchar2(30) NOT NULL;

OUTPUT: The table created from running the ALTER TABLE statement is shown below. Pay close attention to how the newly created column is appended to the end of the table. 

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar2(30) default 'John',
last_name        varchar2(30) default 'Doe',
email            varchar2(60) default 'john.doe@xyz.com',
phone            varchar2(15) default '000-000-0000',
hire_date        date        default '1901-01-01',
sales_id         integer      default 0,
middle_name     varchar2(30) NOT NULL
);

OPTION 2 : Lets look at an example on how to add multiple columns to a table using the ALTER TABLE statement.

ALTER TABLE employees 
ADD
(middle_name varchar2(30) NOT NULL,
suffix varchar2(10) NOT NULL,
dob date);

OUTPUT: The resulting table from executing the above ALTER TABLE statement is shown below. Once again, fields MIDDLE_NAME, SUFFIX and DOB are added to the end of the table. 

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar2(30) default 'John',
last_name        varchar2(30) default 'Doe',
email            varchar2(60) default 'john.doe@xyz.com',
phone            varchar2(15) default '000-000-0000',
hire_date        date        default '1901-01-01',
sales_id         integer      default 0,
middle_name     varchar2(30) NOT NULL,
suffix varchar2(10) NOT NULL,
dob date

);

4. SQL How to add a Column using CREATE TABLE AS

The fourth and final way to add a column to a table is using a CREATE TABLE AS (CTAS) statement. This is an advanced technique and might get you frustrated. But if you are trying to expand your horizon with SQL, definitely give this approach a shot.

CTAS creates a table based on a Select statement from another table. A little-known feature that most developers do not realize is that you can utilize a CTAS statement to SQL add a column to your table. Matter of fact you can add a column anywhere you please – the beginning, middle, end. Dealers choice!

Lets continue working with the EMPLOYEE table, and assume that we want to add a couple of new columns, MIDDLE_NAME, SUFFIX and DOB (date of birth) in the 3rd, 5th and 8th position.

STEP 1: Select the fields you want from your table. In this case we select from the EMPLOYEES table

SELECT employee_id,first_name, last_name,email,phone,hire_date,sales_id FROM employees;

STEP 2: Add columns MIDDLE_NAME, SUFFIX and DOB in the position(s) you want

SELECT employee_id, first_name, middle_name, last_name, suffix, email, phone, dob, hire_date, sales_id  
FROM
employees;

STEP 3: Now comes the tricky part. We have to set a datatype for these new fields. For this we shall leverage the CAST function.  

SELECT 
employee_id,
first_name,
CAST (NULL as VARCHAR2 (30)) as middle_name,
last_name,
CAST (NULL as VARCHAR2 (10)) as suffix,
email,
phone,
CAST (NULL as DATE ) as dob,
hire_date,
sales_id
FROM employees;

STEP 4: The final create table as statement to SQL add columns is listed below. Couple of point to keep in mind.

  • The table created with the new columns has to be named something different initially. In this case we named it “EMPLOYEES_NEW”
  • The new table will contain all the data that exists in the old EMPLOYEES table
  • After creating EMPLOYEES_NEW table, you have to drop the old table using “DROP TABLE EMPLOYEES;”  and rename the table by issuing “RENAME EMPLOYEES_NEW to EMPLOYEES;”
  • Keep in mind, any indexes, constraints and permissions on the initial EMPLOYEES table would needed to be granted again
  • Lastly, please make sure you explore within our own schema and not anywhere near a production environment. 
CREATE TABLE employee_new AS
     SELECT
          employee_id,
          first_name,
          CAST (NULL as VARCHAR2 (30)) as middle_name,
          last_name,
          CAST (NULL as VARCHAR2 (10)) as suffix,
          email,
          phone,
          CAST (NULL as DATE) as dob,
          hire_date,
          sales_id
     FROM employees;

Relational Databases are not the only structures against which you can write SQL. Spark SQL, a module of Apache Spark lets users query structured data using a similar syntax. Amazon Athena, a web service by AWS lets users write SQL against data stored in files. Even though there is a general push for consuming data outside a traditional relational database, the demand for SQL related skills will always exist. 

SQL helpful links

Oracle Database SQL Reference

For Oracle SQL syntax, expressions and function reference

Transact SQL (T-SQL) Reference

For SQL Server transact SQL functions, examples and syntax

MySQL Reference Manual

For MySQL related syntax, statements and examples

PostgreSQL Language Reference

For information related to PostgreSQL data types and functions

Email us at : info@obstkel.com

Copyright 2020 © OBSTKEL LLC. All rights Reserved.
Scroll to Top