SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records into a table in a database.

The basic syntax for the INSERT INTO statement is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

In this syntax, you specify the name of the table that you want to insert data into, followed by the list of column names in parentheses.

The VALUES keyword is followed by the list of values that you want to insert, in the same order as the column names.

Inserting data into a single column

You can also insert data into a single column of a table by specifying only the column name and its value.

The syntax is as follows:

INSERT INTO table_name (column_name)
VALUES (value);

For example, if you have a table named "customers" with a column named "name", you can insert a new name into the table.

The syntax is as follows:

INSERT INTO customers (name)
VALUES ('John Doe');

Inserting data from a SELECT statement

You can also insert data into a table by selecting data from another table or a query.

The syntax is as follows:

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table;

For example, if you have a table named "customers" with columns named "id", "name", and "address", and another table named "orders" with columns named "customer_id", "order_date", and "amount", you can insert data into the "customers" table from the "orders" table.

You can use this query:

INSERT INTO customers (id, name, address)
SELECT customer_id, name, address
FROM orders;

Inserting data with a default value

You can also insert a default value into a column when inserting data into a table.

The syntax is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, DEFAULT, value3, ...);

For example, if you have a table named "employees" with columns named "id", "name", and "salary", you can insert a new employee into the table with a default salary.

This is the query to use:

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', DEFAULT);

Inserting data with the SET clause

You can also use the SET clause to insert data into a table. This method is useful when you only want to insert data into a few columns of the table.

The syntax is as follows:

INSERT INTO table_name
SET column1 = value1, column2 = value2, ...;

For example, if you have a table named "employees" with columns named "id", "name", and "salary", you can insert a new employee into the table

This is the query to use:

INSERT INTO employees
SET id = 1, name = 'John Doe', salary = 50000;

Note that this method is not supported in all SQL databases.

Inserting data with the ON DUPLICATE KEY UPDATE clause

You can also use the ON DUPLICATE KEY UPDATE clause to insert data into a table.

This clause allows you to insert a new record or update an existing record if a duplicate key is found.

The syntax is as follows:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

For example, if you have a table named "products" with a primary key column named "id" and columns named "name" and "price", you can insert a new product into the table or update the price of an existing product.

You can use this query:

INSERT INTO products (id, name, price)
VALUES (1, 'Product A', 10)
ON DUPLICATE KEY UPDATE price = 20;

This query would insert a new product with an id of 1, a name of "Product A", and a price of 10 if the id does not already exist in the table, or update the price of the existing product with id 1 to 20 if the id already exists in the table.

Conclusion

The INSERT INTO statement is a powerful and versatile command for inserting data into a table in a SQL database.

By understanding the different methods and options available, you can easily insert data into a table and keep your database up to date and accurate.

SQL Basics