SQL Insert Statement

The SQL INSERT statement can be used to insert one or more records into a database table as mentioned below.

  • Insert a single record into a table.
  • Insert multiple records into a table all at once.

In the previous chapter, we have created a table that doesn't contain any data now.

Let's write an insert statement to insert records of data into that table.

Syntax

Here is the basic syntax of an INSERT statement.

  • The table name is represented as table_name.
  • The table columns are represented as column1, column2, and ao on.
  • The table column values are represented as value1,value2, and so on.
  • The number of columns and the values must always match.
INSERT INTO table_name (column1,column2,...) VALUES (value1,value2,...);

View Table Structure

We always need to find the table structure to find out the columns before we create an INSERT statement.

In MySQL, use any of the below commands to find the structure of a table.

DESCRIBE table_name;
DESC table_name;

In SQL Server, use the below command to find the structure of a table.

EXEC sp_columns table_name;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000

Add Single Record

We can use an INSERT statement to add a record to a table.

  • Numeric values must not be enclosed within quotes.
  • Non-numeric values like strings, dates, etc., must always be enclosed within quotes.
  • Use the backslash to escape quotes within a non-numeric value.
    • Example: 'The user\'s profile'
  • No need to specify auto-increment or auto-generate columns in the INSERT query, as the database system automatically generates and assigns a value to such fields.

Run this on IDE

INSERT INTO employees (employee_name, gender, birth_date, hire_date, salary)
VALUES ('John Jacobs', 'M', '1996-10-21', '2015-11-10', 63000);

After successful INSERT, the data contains the inserted record.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 John Jacobs M 1996-10-21 2015-11-10 63000

Similarly, we can add any number of records to the table by running a similar kind of insert statement.

Or use the below statement to insert multiple records at once, using a single INSERT statement.

Add Multiple Records

We can use an INSERT statement to insert more than one record at once as shown below.

  • The values of each record must be enclosed with the braces.
  • All the data records must be separated by a comma.

Run this on IDE

INSERT INTO employees (employee_name, gender, birth_date, hire_date, salary)
VALUES 
('Harry Potter', 'M', '1998-05-21', '2017-18-08', 52000),
('Jerry Thomas', 'F', '2001-08-25', '2019-07-07', 49000),
('Alice Conner', 'F', '1999-12-10', '2015-04-04', 55000);

After successful INSERT, the table contains the inserted records.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 Harry Potter M 1998-05-21 2017-18-08 52000
5 Jerry Thomas F 2001-08-25 2019-07-07 49000
6 Alice Conner F 1999-12-10 2015-04-04 55000

Overall

We now know how to create and execute an INSERT statement to insert data into a table.

Related Links