SQL Insert Into Table

In this lesson, you will learn how to insert records in a database table using SQL.

Insert Record In SQL

The INSERT INTO statement is used to insert a new record inside a database table.

Syntax

When inserting a new record, you can specify which columns you want to give values and then you have to set a value for each column respectively.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

However, you can ignore mentioning the name of columns, but then you have to set a value for each column in the table in the same order as you defined them.

INSERT INTO table_name
VALUES (value1, value2, ...);

Preparing A Database

Since you will learn how to add information to tables, you must have a database with at least one table to practice adding data to it. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "users" inside the selected database
CREATE TABLE users (
    username VARCHAR(30),
    email VARCHAR(50),
    phone VARCHAR(20)
);
    

Insert Record Example

The following query inserts a new record in the "users" table.
Note that we did not mention the names of the columns to which we will pass values, but we passed a value to each field in the line.

INSERT INTO users
VALUES ('mhamad', 'mhamad@example.com', '+96170123456');
    

The following query inserts a new record in the "users" table too.
Here we have defined the names of the fields to which we will pass values.

INSERT INTO users (username, email, phone)
VALUES ('hala', 'hala@example.com', '+96301102030');
    

The following query inserts a new record in the "users" table.
Note that we didn't pass values for all columns.

INSERT INTO users (username, email)
VALUES ('adam', 'adam@example.com');
    

Insert Multiple Records

You can insert multiple records at once.

INSERT INTO table_name (column1, column2, ...) VALUES
(value1, value2, ...),    -- This values are for 1 record
(value1, value2, ...),    -- This values are for 1 record
(value1, value2, ...);    -- This values are for 1 record

The following query inserts 3 records in the "users" table.

INSERT INTO users (username, email, phone) VALUES
('jane', 'jane@example.com', '+96101405738'), 
('mia', 'mia@example.com', '+96170005432'), 
('sara', 'sara@example.com', '+96103108808');
    

View Table Data

To view all records in the table use the SELECT statement as follows.

SELECT * FROM table-name;

The following query retrive all rows in the "users" table.

SELECT * FROM users;
    

Insert Records Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "employees" and check whether it exists or not. the table should contain the following fields:

• first_name VARCHAR(50)
• last_name VARCHAR(50)
• salary DECIMAL(7,2)

3- Write a query that inserts 4 rows in the "employees" table.

4- Write a query that deletes the "training" database if it exists.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS employees (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(7,2)
);

INSERT INTO employees (first_name, last_name, salary) VALUES
('Mhamad', 'Harmush', 1200),
('Hala', 'Hassan', 1050),
('Emma', 'Stone', 1180),
('Jim', 'Wilson', 980);

DROP DATABASE IF EXISTS training;