In SQL, the INSERT INTO statement is used to insert new row(s) in a database table. For example,
INSERT INTO Customers(customer_id, first_name, last_name, age, country)
VALUES
(5, 'Harry', 'Potter', 31, 'USA');
Here, the SQL command inserts a new row in the Customers table with the given values.
Note: If we need to insert data from any other existing table , we can use the SQL INSERT INTO SELECT statement.
Insert Row Providing Value Explicitly
It's possible to provide default values to a column (for example, auto incrementing a column). In a database table, the ID field is usually unique auto incremented.
In such cases, we can omit the value for that column during row insertion. For example,
INSERT INTO Customers(first_name, last_name, age, country)
VALUES
('James', 'Bond', 48, 'USA');
Here, the SQL command automatically sets the new customer_id for the new row and inserts it in a table.
Insert Multiple Rows at Once in SQL
It's also possible to insert multiple rows to a database table at once. For example,
INSERT INTO Customers(first_name, last_name, age, country)
VALUES
('Harry', 'Potter', 31, 'USA'),
('Chris', 'Hemsworth', 43, 'USA'),
('Tom', 'Holland', 26, 'UK');
Here, the SQL command inserts three rows to the Customers table.
SQL INSERT Topics
It is also possible to insert values in a row without specifying column names. For example,
INSERT INTO Customers
VALUES
('Chris', 'Evans', 42, 'USA');
Here, the SQL command inserts the new row serially in each column.
Note: If we don't specify column names, the order of column names in the database table must match the order of values in the SQL query.
If we skip column names during row insertion, the values of those columns will be NULL.
INSERT INTO Customers(first_name, last_name, age)
VALUES
('Brad', 'Pitt', 58);
Here, the SQL command sets the country column's value to NULL. However, the ID column will be an incremented value because of auto increment constraint.
Note: If the null values are not allowed for a column, the SQL query results in an error. To learn more, visit NOT NULL Constraint.