SQL - INSERT Query

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

The INSERT statement will only accept the data that follows all the attributes of a column in a table. The data inserted into a table must have same datatypes, satisfy the constraints (if any), etc. If the inserted data does not satisfy any of the attributes, the INSERT INTO statement displays an error.

Syntax

There are two basic syntaxes of the INSERT INTO statement which are shown below.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data.

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

The SQL INSERT INTO syntax will be as follows −

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example

The following statements would create six records in the empty CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

You can also insert multiple rows at once using this query as shown below.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 ), (5,  Hardik , 27,  Bhopal , 8500.00 ), (6,  Komal , 22,  MP , 4500.00 );

You can create a record in the CUSTOMERS table by using the second syntax as shown below.

INSERT INTO CUSTOMERS 
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

Output

The output is displayed as follows −

(7 rows affected)

Verification

To check if the records are inserted into the CUSTOMERS table, use the SELECT query as shown below.

SELECT * FROM CUSTOMERS;

The table will be displayed with all the records included in it.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Populating one table using another table

Sometimes, you just need to copy the data from one existing table in a database to another table in the same database. And there are various ways to do so −

Using INSERT... SELECT

Using INSERT... TABLE

INSERT... SET Statement

You can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table.

Here is the syntax −

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

Example

The following statement would create another table testCUSTOMERS with the same structure as CUSTOMERS table −

CREATE TABLE testCUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Using the INSERT... INTO statement, insert the records in the CUSTOMERS table into testCUSTOMERS table.

INSERT INTO testCUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) SELECT * FROM CUSTOMERS;

Output

The output will be displayed as −

(7 rows affected)

Verification

To verify if the records are inserted properly or not, use the following SELECT query −

SELECT * FROM testCUSTOMERS;

The table must be displayed containing the same records as Customers −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

INSERT... TABLE Statement

On the other hand, instead of selecting specific columns you can insert the contents of one table into another using the INSERT …. TABLE statement.

Following is the syntax to do so −

INSERT INTO table1 TABLE table2;

Example

In this example, let us use the same Customers table we have created in the previous example and copy its contents to another table testCUSTOMERS1. For that, we first try to create the table testCUSTOMERS1 with the same structure as CUSTOMERS table −

CREATE TABLE testCUSTOMERS1(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Using the Customers table, we will try to insert all its values into testCUSTOMERS1 table −

INSERT INTO Customers TABLE testCUSTOMERS1;

Output

This query will generate the following output −

(7 rows affected)

Verification

If you verify the contents of the testCUSTOMERS1 table using the SELECT statement shown below −

SELECT * FROM testCUSTOMERS1;

The table will be displayed with the newly inserted values as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+