Table of Contents
- Preliminary operations
- How to create a new table in SQL
- How to create a new table if it does not exist in SQL
- How to create a new table from another table in SQL
CREATE TABLE command is used to create a new table in SQL.
This tutorial will show you the command syntax and several practical examples to best learn how to use it.
You probably already know this, but I just want to remember that before creating a table you need to create a database and select it, otherwise you may run into some errors.
Here are the commands for these two operations:
CREATE DATABASE devinsimplewords; USE devinsimplewords;
If you are in trouble check this post!
How to create a new table in SQL
Creating a new table in SQL is a simple task if you know how to do it.
The command has the following syntax:
CREATE TABLE table_name ( column_name_1 type, column_name_2 type, column_name_3 type, .... column_name_n type );
Starting from this syntax, suppose we want to create a
Users table like this:
|Column Name||Column Type|
Given this information we can create the table using this command:
CREATE TABLE Users ( id int, name varchar(255), surname varchar(255), city varchar(255) );
How to create a new table if it does not exist in SQL
In the previous section we saw how to create a table, in particular we created the
But what happens if we try to repeat the same command?
SQL does not allow us to create two tables with the same name, so an error is raised.
However in SQL it is possible to slightly modify the
CREATE TABLE command to work around this problem.
CREATE TABLE if not exists Users( id int, name varchar(255), surname varchar(255), city varchar(255) );
if not exists in fact we can run the same command several times and the table will be created only the first time.
Mistakes are gone too!
How to create a new table from another table in SQL
Using SQL it is possible to create a new table starting from an existing one, basically we are talking about a copy.
Keep in mind that not only the structure will be duplicated, but also the data (if any).
The syntax is the following:
CREATE TABLE table_name AS SELECT column_1, column_2,..., column_n | * FROM existing_table_name WHERE <condition>;
Let’s now see the command to execute:
CREATE TABLE Users2 AS ( SELECT * FROM Users );
Here we are at the end of this tutorial, as always I hope it was useful to you and that now you know how to best manage each scenario for creating new tables using SQL.
If you are in trouble, do not hesitate to write me in the comments, otherwise take a look at the latest posts!