SQL - Insert Query

The INSERT statement in SQL is used to insert new rows into a table. It allows you to add data into a table by specifying the column names and the corresponding values for the new row. The INSERT statement is an essential part of data manipulation in SQL and is used to populate tables with new records.

Syntax:

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

Alternatively, you can omit the column names if you're inserting values for all the columns in the table. In that case, the syntax becomes:

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

Example:

Let's assume we have a "Customers" table with the following columns:

CustomerID (unique identifier for each customer)

FirstName (first name of the customer)

LastName (last name of the customer)

Email (email address of the customer)

Phone (phone number of the customer)

To insert a new customer into the "Customers" table, we can use the INSERT statement:

INSERT INTO Customers (FirstName, LastName, Email, Phone)
VALUES ('John', 'Doe', '[email protected]', '123-456-7890');

Explanation:

The INSERT INTO keyword is followed by the name of the table where you want to insert data. In this case, it's the "Customers" table.

The column names are specified within parentheses after the table name. In this example, we provide the column names FirstName, LastName, Email, and Phone.

The VALUES keyword is followed by a list of corresponding values for the columns specified. The values must be provided in the same order as the column names. In this case, we provide the values 'John' for FirstName, 'Doe' for LastName, '[email protected]' for Email, and '123-456-7890' for Phone.

By executing this query, a new row will be inserted into the "Customers" table with the specified values for the columns.

Inserting Values into All Columns:

If you want to insert values for all the columns in the table, you can omit the column names in the INSERT statement. In that case, the syntax becomes:

INSERT INTO Customers
VALUES ('John', 'Doe', '[email protected]', '123-456-7890');

It's important to ensure that the number and order of the values provided match the number and order of the columns in the table.

Adjust the table name, column names, and values as per your specific database schema and requirements. The INSERT statement is fundamental for adding new records to a table in SQL.

Example : Inserting Multiple Rows

You can use a single INSERT statement to insert multiple rows into a table by specifying multiple sets of values separated by commas. For example, to insert two new customers into the "Customers" table, you can use the following query:

INSERT INTO Customers (FirstName, LastName, Email, Phone)
VALUES ('John', 'Doe', '[email protected]', '123-456-7890'),
       ('Jane', 'Smith', '[email protected]', '987-654-3210');

This query will insert two new rows into the "Customers" table with the specified values for each column.

Example : Inserting Values from Another Table

You can insert values into a table from another table using the INSERT INTO ... SELECT statement. For example, let's say we have an "Employees" table with columns EmployeeID, FirstName, LastName, and Salary, and we want to insert the employees with a salary above a certain threshold into a new table called "HighPaidEmployees". The query would look like this:

INSERT INTO HighPaidEmployees (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

This query selects the rows from the "Employees" table where the salary is above 50000 and inserts them into the "HighPaidEmployees" table.

Example : Inserting Values with Default or NULL

If a column in the table has a default value specified or allows NULL values, you can omit the corresponding value in the INSERT statement. For example, let's say the "Customers" table has a column called Address that allows NULL values. To insert a new customer without specifying the address, you can use the following query:

INSERT INTO Customers (FirstName, LastName, Email, Phone)
VALUES ('John', 'Doe', '[email protected]', '123-456-7890');

In this case, the Address column will either use the default value specified or store NULL.

These examples demonstrate some common scenarios when using the INSERT statement. Adjust the table name, column names, and values as per your specific database schema and requirements. The INSERT statement provides flexibility in inserting new rows into a table, allowing you to add individual or multiple rows and even insert values from another table.