SQL Basics | DML: Data Manipulation Language | INSERT

Basically, there are two ways to insert data into a table. One inserts one row at a time. The other inserts multiple rows at once.

INSERT syntax

The most basic SQL for inserting data is as follows.

INSERT INTO "table_name" ("field1", "field2", ...)
VALUES ("value1", "value2", ...);

If the order of the fields in the table matches the order of the entered values, the field names can be omitted.

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

You can also insert multiple rows at once with SELECT. Unlike the previous example, the SELECT command specifies the data to insert into the table. If you are wondering whether that means the data comes from another table, that is not necessarily the case. SQL for inserting multiple rows at once is as follows.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

This is the most basic form. The SQL statement can also include joins, aliases, and clauses such as WHERE, GROUP BY, and HAVING.

INSERT example: inserting one row

Suppose you have a table with the following structure.

store_information table

To insert sales data of $900 for the Los Angeles store on Jan-10-2018 into this table, enter the following SQL statement.

INSERT INTO store_information (store_name, sales, txn_Date)
VALUES ('Los Angeles', 900, 'Jan-10-2018');

Or you can enter it as follows.

INSERT INTO store_information VALUES ('Los Angeles', 900, 'Jan-10-2018');

INSERT example: inserting multiple rows

Suppose you want to insert sales data from 2017 into the store_information table, and that data is obtained from the sales_information table. Enter the following SQL.

INSERT INTO store_information (store_name, sales, txn_date)
SELECT store_name, sales, txn_date
FROM sales_information
WHERE Year(txn_date) = 2017;

Here, a SQL Server function is used to retrieve the year from the date, but the syntax differs by database. For example, in Oracle, you can use WHERE TO_CHAR(Txn_Date, 'yyyy') = 2017.