Insert Query in SQL - Javagyansite

In this article, we will learn about “Insert Query in SQL”. Let’s get started.

Table of Contents

Insert Query in SQL Syntax

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

Example:

Let’s say we have a table called “Customers” with the following structure:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

You want to insert a new customer into this table:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

In this example:

INSERT INTO Customers specifies that you’re inserting data into the “Customers” table.
(CustomerID, FirstName, LastName, Email) lists the columns into which you want to insert data.
VALUES (1, 'John', 'Doe', 'john.doe@example.com') specifies the values you want to insert into the respective columns.

After executing this query, a new row will be added to the “Customers” table with the provided data.

Inserting Multiple Rows in Table using Insert Statement in SQL

To insert multiple rows into a table using the INSERT statement in SQL, you can provide multiple sets of values within a single INSERT statement. Here’s an example using the “Customers” table:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (2, 'Jane', 'Smith', 'jane.smith@example.com'),
    (3, 'Michael', 'Brown', 'michael.brown@example.com'),
    (4, 'Sarah', 'Johnson', 'sarah.johnson@example.com');

In this example, we’re inserting three rows into the “Customers” table with the specified values for each column. Each set of values is enclosed in parentheses and separated by commas.

After executing this query, three new rows will be added to the “Customers” table with the provided data.

Inserting Data from Another Table

To insert data from one table into another table in SQL, you can use the INSERT INTO statement combined with a SELECT statement to specify the source table. Here’s an example:

Suppose you have two tables, “source_table” and “target_table,” and you want to insert data from “source_table” into “target_table.” Both tables have the same structure with columns: “column1,” “column2,” and “column3.”

Here’s how you can insert data from “source_table” into “target_table”:

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

Here’s a more concrete example:

Let’s say you have a “customers” table and a “new_customers” table with the same structure. You want to insert all customers from the “new_customers” table into the “customers” table.

INSERT INTO customers (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email
FROM new_customers;

This SQL query will insert all the customer data from “new_customers” into the “customers” table, preserving the structure and data mapping between the two tables.

Inserting data into a table with an auto-increment primary key

When you have a table with an auto-increment primary key (often referred to as an identity column in SQL Server, or a serial column in PostgreSQL), you don’t need to specify a value for that primary key column when inserting data. The database system will automatically generate a unique value for it. Here’s an example:

Let’s assume you have a table called “products” with the following structure:

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

In this table, “product_id” is an auto-increment primary key, “product_name” is a required field, and “price” represents the price of the product.

To insert data into this table, you can omit the “product_id” column because it will be automatically generated. Here’s an example of inserting a new product:

INSERT INTO products (product_name, price)
VALUES ('Widget', 19.99);

In this SQL query:

We specified the columns we want to insert data into, which are “product_name” and “price.”
We provided the corresponding values for these columns, which are ‘Widget’ for “product_name” and 19.99 for “price.”

The database system will automatically generate a unique value for “product_id” because it’s an auto-increment primary key.

Inserting Data with Default Values

When inserting data into a table in SQL, you can set default values for columns if no value is provided during the insertion. This is particularly useful when you have columns that often take the same default values. Here’s an example of inserting data with default values:

Let’s assume you have a table called “students” with the following structure:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    enrollment_year INT DEFAULT 2023
);

In this table, “student_id” is an auto-increment primary key, “first_name” and “last_name” are required fields, and “enrollment_year” has a default value of 2023.

To insert data into this table, you can omit the “enrollment_year” column because it has a default value defined. Here’s an example of inserting a new student:

INSERT INTO students (first_name, last_name)
VALUES ('John', 'Doe');

In this SQL query:

We specified the columns we want to insert data into, which are “first_name” and “last_name.”
We provided the corresponding values for these columns, which are ‘John’ for “first_name” and ‘Doe’ for “last_name.”

We didn’t provide a value for the “enrollment_year” column because it has a default value of 2023.

After executing this SQL statement, a new row will be added to the “students” table with a unique “student_id” (auto-generated), the first name ‘John,’ the last name ‘Doe,’ and the enrollment year 2023 as the default value.

If you want to explicitly set a value for “enrollment_year” different from the default, you can include it in the INSERT statement:

INSERT INTO students (first_name, last_name, enrollment_year)
VALUES ('Jane', 'Smith', 2022);

In this case, the enrollment year for the student ‘Jane Smith’ will be set to 2022 instead of the default 2023.

Best Practices for using Insert Statement in SQL

Using the INSERT statement in SQL is a common operation for adding data to a database. To ensure that your SQL INSERT statements are efficient, maintainable, and secure, consider the following best practices:

Specify Columns Explicitly:

Always specify the columns you’re inserting data into. This makes your intent clear and ensures that your query remains valid even if the table structure changes. Avoid using the shorthand INSERT INTO table VALUES (…) unless it’s necessary.

Use Parameterized Queries:

When inserting data from user inputs or external sources, use parameterized queries or prepared statements to prevent SQL injection attacks and improve security.

Batch Inserts:

When inserting multiple rows, use batch inserts for better performance. Instead of inserting one row at a time, insert multiple rows in a single SQL statement.

Use Transactions:

Wrap multiple INSERT statements within a transaction to ensure data consistency. This allows you to commit or rollback changes as a single unit of work.

Conclusion: Insert Query in SQL

This article provided a comprehensive overview of the “Insert Query in SQL” along with various practical examples and best practices for using the INSERT statement effectively. Here are the key takeaways:

Insert Query Syntax: The article explained the basic syntax of the INSERT statement in SQL, demonstrating how to insert data into specific columns of a table using the VALUES clause.
Inserting Multiple Rows: It illustrated how to insert multiple rows into a table within a single INSERT statement, improving efficiency when adding multiple records.
Inserting Data from Another Table: The article covered how to insert data from one table into another using the INSERT INTO statement in combination with a SELECT statement, maintaining data integrity and structure.
Auto-Increment Primary Keys: It explained how to insert data into a table with an auto-increment primary key, allowing the database to generate unique values automatically.
Inserting Data with Default Values: The article showed how to set default values for columns, simplifying the insertion process when certain columns often take the same default values.
Best Practices: The article provided essential best practices for using the INSERT statement, emphasizing explicit column specification, parameterized queries for security, batch inserts for performance, and the use of transactions for data consistency.

By following these guidelines and understanding the different aspects of SQL INSERT statements, you can efficiently add data to your database while ensuring data integrity and security.

Related Articles :