Inserting Columns at a Specific Position in an Existing SQL Table

1. Introduction

The order of columns in an SQL table has little or no bearing on performance. However, to make data in our table more readable and easier to understand, we sometimes have to reorder its columns.

In this tutorial, we explore how to insert columns at a specific position in an existing SQL table.

2. Inserting a Column at the End of an SQL Table

Generally, to insert a column in an SQL table, we combine the ALTER TABLE statement with an ADD clause:

ALTER TABLE table ADD column datatype;

By default, the statement above will add a new column to the end of our SQL table.

To illustrate the insertion of columns in an SQL table, we’ll create a two-column table named Baeldung in our pre-existing database:

CREATE TABLE Baeldung (first_column int, second_column varchar(150));

We can confirm the current order of the columns in our table by querying information_schema.columns:

SELECT column_name FROM information_schema.columns WHERE table_name = 'Baeldung';
+------------------+
| COLUMN_NAME      |
+------------------+
| first_column     |
| second_column    |
+------------------+

Now, let’s add a third column named third_column to the end of the Baeldung table:

ALTER TABLE Baeldung ADD third_column varchar(100);

To confirm the addition of third-column to the end of the table, we’ll query information_schema.columns:

SELECT column_name FROM information_schema.columns WHERE table_name = 'Baeldung';
+------------------+
| COLUMN_NAME      |
+------------------+
| first_column     |
| second_column    |
| third_column     |
+------------------+

The output shows that third_column now appears as the last column in the table.

3. Inserting a Column at a Specific Position of an SQL Table

Generally, we can add a column at a specific position of an SQL table by recreating the table with the column at the desired position. One way to achieve this would be to create a new table while specifying the columns in a desired order and transfer data from the existing table to the new table.

For instance, if we want to add a column named new_second_column to the second position of the Baeldung table, we’ll create a second table as follows:

CREATE TABLE Baeldung2 (first_column int, new_second_column date, second_column varchar(150), third_column varchar(100));

After that, we’ll transfer data from the old table to the new table:

INSERT INTO Baeldung2 (first_column, second_column, third_column) SELECT first_column, second_column, third_column FROM Baeldung;

Then, we’ll drop the old table:

DROP TABLE Baeldung;

Next, we rename the new table:

ALTER TABLE Baeldung2 RENAME TO Baeldung;

The table-renaming statement above will work on MySQL and PostgreSQL databases. But in SQL Server, we should run this statement instead:

EXEC sp_rename 'Baeldung2', 'Baeldung';

Once again, we’ll verify if Baeldung now has our columns in the desired order:

SELECT column_name FROM information_schema.columns WHERE table_name = 'Baeldung';
+-------------------+
| COLUMN_NAME       |
+-------------------+
| first_column      |
| new_second_column |
| second_column     |
| third_column      |
+-------------------+

As expected, new_second_column now sits in the second position.

4. Inserting a Column at First Position in a MySQL Table

Besides recreating the table and restoring data to it, we can insert a column in the first position of a MySQL table, using the FIRST keyword:

ALTER TABLE table ADD column datatype FIRST;

To show how to insert a column in the first position of an SQL table, we’ll add a column named new_first_column to our Baeldung table:

ALTER TABLE Baeldung ADD new_first_column date FIRST;

Now, we confirm our addition:

SELECT column_name FROM information_schema.columns WHERE table_name = 'Baeldung';
+------------------+
| COLUMN_NAME      |
+------------------+
| new_first_column |
| first_column     |
| new_second_column|
| second_column    |
| third_column     |
+------------------+

As apparent from the output, new_first_column is now the first column of the Baeldung table.

5. Inserting a Column at Any Position in a MySQL Table

To insert a column at any position in a MySQL table, we can also use the AFTER keyword:

ALTER TABLE table ADD column datatype AFTER existing_column;

Let’s add a column named fourth_column after third_column in the Baeldung table:

ALTER TABLE Baeldung ADD fourth_column varchar(100) AFTER third_column;

Now, let’s verify the change:

SELECT column_name FROM information_schema.columns WHERE table_name = 'Baeldung';
+-------------------+
| COLUMN_NAME       |
+-------------------+
| new_first_column  |
| first_column      |
| new_second_column |
| second_column     |
| third_column      |
| fourth_column     |
+-------------------+

The output shows that fourth_column now appears after third_column in the Baeldung table.

6. Conclusion

In this article, we saw that we can generally insert a column at the end of an SQL table using the ALTER TABLE statement with an ADD clause. However, when it comes to inserting columns at specific positions in a table, we may have to recreate the table and restore data to it in most cases.

Notably, MySQL allows us to insert columns at the first position and any other position when we use the FIRST and AFTER keywords with ALTER TABLE, respectively.