Data entry forms the backbone of any database management process. For SQL beginners, the SQL INSERT statement is the gateway to effortlessly adding new data to your carefully crafted tables. In this comprehensive article, we will learn the ins and outs of SQL INSERT. You'll learn how to insert single and multiple rows of data, explore the powerful SELECT statement for data insertion, and gain insights into handling potential errors that may arise during the process.
Table of content:
6. Conclusion
What is SQL INSERT?
The SQL INSERT statement is used to insert new rows into a table. It is one of the most basic and important SQL statements.
Syntax
The basic syntax of the SQL INSERT statement is as follows:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);SQL INSERT Single Row
To insert the value we first need to create a table. I have created a table as CustomersData with columns as Customer_ID, Customer_Name, Email_ID, and Country.
CREATE TABLE CustomersData (
Customer_ID INT PRIMARY KEY,
Customer_Name VARCHAR(100),
Email_ID VARCHAR(255),
Country VARCHAR(20)
);Now, we will insert a new row in the CustomersData table, specifying the values for the Customer _ID, Customer_Name, Email_ID, and Country.
INSERT INTO CustomersData (Customer_ID, Customer_Name, Email_ID, Country)
VALUES (1001, 'Rahul Roy', 'RahulRoy@gmail.com', 'INDIA');There are two ways to use the SQL INSERT statement:
Specifying column names: In this case, you must specify the names of the columns in the table where you want to insert the data.
Not specifying column names: In this case, the data will be inserted into the table in the same order as the columns in the table.
Specifying Values: In this case, the data will be inserted into the table with literal values, variables, or expressions.
1. Specifying Column Names
If you specify the names of the columns in the SQL INSERT statement, the order of the values must match the order of the columns. For example, the following statement would insert a new row into the customer's table, specifying the values for the name and email columns:
INSERT INTO CustomersData (Customer_ID, Customer_Name, Email_ID, Country)
VALUES (1005, 'Mandeep Kaur', 'Mandeep@gmail.com', 'CANADA');2. Not Specifying Column Names
if you do not specify column names, the order of the values must match the order of the columns in the table. If the order of the values does not match the order of the columns, an error will occur.
INSERT INTO CustomersData
VALUES (1010, 'Ayushi Verma', 'Ayushiverma@gmail.com', 'INDIA');3. Specifying values
The values that you specify in the SQL INSERT statement can be literal values, variables, or expressions.
Literal values: Literal values are simply the data that you want to insert into the table. For example, the following statement would insert the literal value 'Satpreet Singh' into the name column:
INSERT INTO CustomersData (Customer_Name) VALUES ('Satpreet Singh'); Variables: Variables are named placeholders that can be used to store data. For example, the following statement would insert the value of the variable @name into the name column:
INSERT INTO customers (name) VALUES (@name); Expressions: Expressions are combinations of literal values, variables, and operators. For example, the following statement would insert the current date and time into the created_at column:
INSERT INTO customers (created_at) VALUES (CURRENT_TIMESTAMP);SQL INSERT Multiple Rows
You can use the SQL INSERT statement to insert multiple rows into a table at the same time. To do this, you simply need to separate the values for each row with a comma. For example, the following statement would insert two new rows into the customer's table:
INSERT INTO CustomersData (Customer_ID, Customer_Name, Email_ID, Country)
VALUES (1003, 'Peter', 'Peter@gmail.com', 'USA'),
(1012, 'John De', 'Johnde@gmail.com', 'Australia'),
(1007, 'Aishwarya', 'Aishwarya@gmail.com', 'INDIA'),
(1016, 'Bob', 'bob@gmail.com', 'America'); SQL INSERT Data using SELECT statement
Using the SELECT statement to insert data into a table in SQL is a powerful and flexible feature that allows you to copy data from one table into another or to manipulate and transform data before inserting it.
The SELECT statement in SQL serves multiple purposes when inserting data into a table:
Copying Data: Easily duplicate or move data from one table to another by selecting specific columns or rows and inserting them into the new table.
Aggregating Data: Summarize or manipulate data from one or more tables before inserting it, performing calculations, or grouping as needed.
Joining Tables: Combine data from different tables using JOIN clauses before inserting it into the destination table.
Data Transformation: Modify or format data before insertion, applying functions or calculations to the selected data.
Filtering Data: Use WHERE clauses to filter specific rows from a source table based on conditions, inserting only the relevant data.
Restructuring Data: Control the order and structure of the data being inserted into the target table, useful when columns' ordering or data types differ between the source and target tables.
In this scenario, you have two tables: "CustomersData" and "Customers." You want to select data from the "Customers" table and insert it into the "CustomersData" table using the SELECT statement.
Assuming both tables have columns named "Customer_ID," "Customer_Name," and "Country," the SQL query to achieve this task is as follows:
INSERT INTO CustomersData (
Customer_ID,
Customer_Name,
Country
)
SELECT
Customer_ID,
Customer_Name,
Country
FROM Customers; The data from the "Customers" table that matches the SELECT criteria will be inserted into the "CustomersData" table. The columns' order in the INSERT INTO clause should match the order of columns in the SELECT statement.
Common Errors
There are a few common errors that can occur when using the SQL INSERT statement. These errors include:
Column name not found: This error occurs if you specify a column name that does not exist in the table.
Data type mismatch: This error occurs if the data that you are trying to insert does not match the data type of the column.
Duplicate entry: This error occurs if you try to insert a row that already exists in the table.
Conclusion
The SQL INSERT statement is a powerful tool that can be used to insert new rows into a table. By following the syntax and examples in this article, you should be able to use the SQL INSERT statement to insert data into your tables with ease.
Comments