The SQL INSERT INTO statement is used to insert new records into a database table. The INSERT INTO statement is used to add new rows of data to a table in a database.
Syntax
The syntax for the SQL INSERT INTO statement is as follows:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Where:
- table_name is the name of the table where you want to insert the new data.
- column1, column2, column3, … are the names of the columns you want to insert data into. You can specify any number of columns. The order of the columns must match the order of the values you want to insert.
- value1, value2, value3, … are the values you want to insert into the columns. The number of values must match the number of columns specified.
Example of SQL INSERT INTO
Consider the following example where you want to insert a new customer into the customers table:
INSERT INTO customers (id, name, city) VALUES (1, 'John Doe', 'New York');
The above SQL statement will insert a new customer with an id of 1, name of ‘John Doe’, and a city of ‘New York’ into the customers table.
SQL INSERT INTO with SELECT Statement
The SQL INSERT INTO statement can also be used to insert data into a table from another table. The syntax for the SQL INSERT INTO with SELECT statement is as follows:
INSERT INTO table1 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table2;
Where:
- table1 is the name of the table where you want to insert the data.
- column1, column2, column3, … are the names of the columns you want to insert data into. You can specify any number of columns. The order of the columns must match the order of the columns in the SELECT statement.
- table2 is the name of the table you want to retrieve the data from.
Example of SQL INSERT INTO with SELECT
Consider the following example where you want to insert all the customers from the customers_backup table into the customers table:
INSERT INTO customers (id, name, city) SELECT id, name, city FROM customers_backup;
The above SQL statement will insert all the customers from the customers_backup table into the customers table.
SQL INSERT INTO with DEFAULT Values
The SQL INSERT INTO statement can also be used to insert data into a table with default values. The syntax for the SQL INSERT INTO with DEFAULT values is as follows:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (DEFAULT, DEFAULT, DEFAULT, ...);
Where:
- table_name is the name of the table where you want to insert the default values.
- column1, column2, column3, … are the names of the columns you want to insert default values into. You can specify any number of columns. The order of the columns must match the order of the values you want to insert.
Example of SQL INSERT INTO with DEFAULT Values
Consider the following example where you want to insert a new customer into the customers table with default values:
INSERT INTO customers (id, name, city) VALUES (DEFAULT, 'Jane Doe', DEFAULT);
The above SQL statement will insert a new customer with a default id, name of ‘Jane Doe’, and a default city into the customers table.
SQL INSERT INTO with ON DUPLICATE KEY UPDATE
The SQL INSERT INTO statement can also be used with the ON DUPLICATE KEY UPDATE clause to update existing records if the new record already exists in the table. The syntax for the SQL INSERT INTO with ON DUPLICATE KEY UPDATE is as follows:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...) ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, column3 = value3, ...;
Where:
- table_name is the name of the table where you want to insert the new data.
- column1, column2, column3, … are the names of the columns you want to insert data into. You can specify any number of columns. The order of the columns must match the order of the values you want to insert.
- value1, value2, value3, … are the values you want to insert into the columns. The number of values must match the number of columns specified.
Example of SQL INSERT INTO with ON DUPLICATE KEY UPDATE
Consider the following example where you want to insert a new customer into the customers table, but if the customer already exists, you want to update their city:
INSERT INTO customers (id, name, city) VALUES (1, 'John Doe', 'Los Angeles') ON DUPLICATE KEY UPDATE city = 'Los Angeles';
The above SQL statement will insert a new customer with an id of 1, name of ‘John Doe’, and a city of ‘Los Angeles’ into the customers table. If a customer with an id of 1 already exists in the table, their city will be updated to ‘Los Angeles’.
Conclusion
In conclusion, the SQL INSERT INTO statement is used to insert data into a database table. The statement can have default values or be used with the ON DUPLICATE KEY UPDATE clause to update existing records. It is important to ensure that the number of columns and values match and that the order of columns matches the order of values when using the statement.
