SQL INSERT INTO Statement

---

---

SQL INSERT INTO Statment

The INSERT INTO statement in SQL is used to add new records to a database table. You can insert data into all columns or specify only certain columns, depending on your needs.

When using this statement, it is important to make sure that each value matches the data type and constraints (such as NOT NULL, UNIQUE, or PRIMARY KEY) of the corresponding column. Also, the number of values should match the total columns in the table or those specified in the query. Otherwise, the database will throw an error and reject the insert.

Syntax

Following is the basic syntax to insert records into all columns in SQL:

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

Example

To see an example, let us create a table with name CUSTOMERS in the MySQL database using the CREATE TABLE statement as shown below:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY (ID)
);

The following SQL INSERT INTO statements will create three records in the empty CUSTOMERS table:

INSERT INTO CUSTOMERS
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS 
VALUES (3, 'Kaushik', 23, 'Kota', 2000.00 );

We get the following output after each insert:

Query OK, 1 row affected (0.00 sec)

Verifying the Insert

After inserting new rows into the table, you can use the SELECT statement to check if the data was added successfully. This helps confirm that the record is now part of the table:

SELECT * FROM CUSTOMERS; 

This command will display all the records from the CUSTOMERS table, including the one you just inserted:

Insert Data into Specific Columns

Sometimes, you may not have data for every column in the table, or you may only want to insert values into certain columns. In that case, you can insert data into specific columns by mentioning their names.

This way, only the columns you list will receive values, and the rest will either use their default values or stay empty (if allowed).

Syntax

Following is the basic syntax to insert records into all columns in SQL:

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

Example

In this example, only the required fields are inserted. The ADDRESS and SALARY columns in the CUSTOMERS table will remain NULL unless a default is defined:

INSERT INTO CUSTOMERS (ID, NAME, AGE)
VALUES (4, 'Chaitali', 25);

Verification

To verify that the data has been inserted correctly into the specified columns, you can run the following SQL SELECT query:

SELECT * FROM CUSTOMERS; 

This will display all the records from the CUSTOMERS table, including the one you just inserted:

Inserting Multiple Rows at Once

You can also insert multiple rows in a single statement instead of adding one row at a time. This is helpful when you need to add a lot of data quickly. Each set of values represents one row, and all the rows are added to the table at once. This helps in saving execution time.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES 
(value1a, value2a, ...),
(value1b, value2b, ...),
(value1c, value2c, ...);

Example

In the example below, we insert three more records into the CUSTOMERS table using a single SQL statement:

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES 
(5, 'Hardik', 27, 'Bhopal', 1800.00),
(6, 'Komal', 22, 'Hyderabad', 1600.00),
(7, 'Muffy', 24, 'Pune', 1900.00);

After executing this command, you will see the following output for each row inserted:

Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

Verification

You can verify the newly inserted records by selecting all rows from the CUSTOMERS table:

SELECT * FROM CUSTOMERS;

This will now display all the records including the three new rows:

Inserting Data from One Table into Another Table

SQL also allows you to insert data into a table by copying it from another table. This is useful when transferring data between tables, backing up records, or transforming data for analysis. We use the INSERT INTO ... SELECT statement for this purpose.

Insert All Columns from Another Table

If both tables have the same structure (same number of columns and compatible data types), you can copy all rows and columns from one table to another using a simple INSERT INTO ... SELECT * statement.

Syntax

Following is the basic syntax to insert all columns from one table to another in MySQL database:

INSERT INTO target_table
SELECT * FROM source_table;

Example

Assume you have another table named NEW_CUSTOMERS with the same structure as the CUSTOMERS table. You can insert all records from CUSTOMERS into NEW_CUSTOMERS as shown below:

INSERT INTO NEW_CUSTOMERS
SELECT * FROM CUSTOMERS;

We get the output as shown below:

Query OK, 0 rows affected (0.04 sec)

Verification

You can use the SELECT query to verify if the records have been copied:

SELECT * FROM NEW_CUSTOMERS;

You can see in the following table the the records have been inserted into the NEW_CUSTOMERS table:

Insert Specific Columns from Another Table

If you only want to copy certain columns from one table to another, you can specify those columns in both the INSERT INTO and SELECT parts of the statement. This is useful when the tables don't have exactly the same structure.

Syntax

Following is the basic syntax to insert specific columns from one table to another:

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

Example

Suppose the CUSTOMERS table contains the same column names, but you only want to copy ID, NAME, and AGE into NEW_CUSTOMERS:

INSERT INTO NEW_CUSTOMERS (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM CUSTOMERS;

Following is the output obtained:

Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

Again, you can verify the data by running the SELECT command on the NEW_CUSTOMERS table:

SELECT * FROM NEW_CUSTOMERS;

Following is the table produced:

Insert Specific Rows Based on Condition

You can also insert only specific rows from one table into another based on a condition. This is done by adding a WHERE clause to the SELECT part of the statement. It is useful for filtering data before inserting it.

Syntax

Following is the basic syntax to insert specific rows based on a condition from one table to another:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Example

Here, we only insert rows where the AGE is greater than 30 from CUSTOMERS into NEW_CUSTOMERS table:

INSERT INTO NEW_CUSTOMERS (ID, NAME, AGE)
SELECT ID, NAME, AGE
FROM CUSTOMERS AS C
WHERE AGE > 30
  AND NOT EXISTS (
    SELECT 1 FROM NEW_CUSTOMERS AS N
    WHERE N.ID = C.ID
  );

We get the output as shown below:

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

To avoid inserting duplicate entries that already exist in the target table, we can use a NOT EXISTS condition in the query. This ensures that only rows from the CUSTOMERS table which do not already exist in NEW_CUSTOMERS based on the ID column are inserted.

Verification

To confirm that the data has been inserted correctly (and without duplication), you can run the following query:

SELECT * FROM NEW_CUSTOMERS;

The table below shows the final state of the NEW_CUSTOMERS table. You can see that no duplicate rows were inserted, and the data remains consistent:

Important Points About INSERT INTO Statement

When you use the INSERT INTO statement in SQL, it is important to follow certain rules to make sure the data is inserted correctly and without errors:

• Make sure the number and order of values match the columns specified in the statement.
• If you skip some columns, those columns will be assigned their default values, or NULL if no default is set.
• Always provide values that match the correct data types for each column to prevent data type mismatch errors.