The SQL INSERT INTO Statement

The SQL INSERT INTO Statement is used to add new rows of data into a table in the database. Almost all the RDBMS provide this SQL query to add the records in database tables.

Each value in the records we are inserting in a table using this statement should be of the same datatype as the respective column and satisfy the constraints of the column (if any). The values passed using an insert statement should match the number of columns in the table or, the number of columns mentioned in the current query. If any of these conditions are not satisfied, this statement generates an error.

Syntax

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

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

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

There is another syntax of INSERT INTO statement where you can specify only column values without column names. But, make sure the order of the values is in the same order as the columns in the table.

Following is second syntax of the SQL INSERT Query −

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

Example

To see an example, let us create a table with name CUSTOMERS in the MySQL database using the CREATE TABLE statement as shown below −

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

The following SQL INSERT INTO statements will create three 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 );

We can also insert multiple rows at once using the following query as shown below −

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

Following query adds another record in the CUSTOMERS table using the second syntax as shown below −

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

Verification

To check if the records are inserted into the CUSTOMERS table, use the SELECT query −

SELECT * FROM CUSTOMERS;

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

Inserting Data into a Table Using Another

Sometimes, you just need to copy the data from an existing table to another table in the same database. SQL provides convenient ways to do so −

• Using INSERT… SELECT
• Using INSERT… TABLE

The INSERT… SELECT Statement

You can populate the data into a table through the select statement using an already existing 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 [(column_name(s))]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

Example

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

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

Now using the INSERT… INTO statement, let us insert all the records from the CUSTOMERS table into the BUYERS table.

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

Output

The output will be displayed as −

Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

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

SELECT * FROM BUYERS;

The table will be displayed containing the same records as CUSTOMERS −

The INSERT… TABLE Statement

If you have two tables structure exactly same, then 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 first_table_name TABLE second_table_name;

Example

In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents into another table named SHOPPERS. For that, let”s create the table SHOPPERS with the same structure as CUSTOMERS table −

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

Now use the following statement to insert all the records from the CUSTOMERS table into SHOPPERS table −

INSERT INTO SHOPPERS TABLE CUSTOMERS;

Output

This query will generate the following output −

Query OK, 7 rows affected (0.13 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

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

SELECT * FROM SHOPPERS;

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

Insert Data Only in Specified Columns

You can select only particular columns from a table to insert into another table. The following SQL statement will insert a new record into BUYERS table with “ID”, “NAME” and “AGE” from CUSTOMERS table.

We can skip only fields which are not defined as NOT NULL, but if we have defined a column as NOT NULL, then we need to provide a value to this column otherwise it will raise an error and record will not be inserted.

Before we proceed further let”s clean all the records from BUYERS table as follows:

DELETE FROM BUYERS;

Now we have empty BUYERS table, let”s use the following SQL statement:

INSERT INTO BUYERS (ID, NAME, AGE) 
SELECT ID, NAME, AGE FROM CUSTOMERS;

Output

This query will generate the following output −

Query OK, 7 rows affected (0.13 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

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

SELECT * FROM BUYERS;

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