Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL INSERT INTO
The INSERT INTO statement is used to add new rows of data to a table in SQL. It allows you to insert values into specific columns or into all columns if values are provided for each.
Basic Syntax
Syntax:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
Example:
Inserting a new record into the employees table with columns: employee_id, first_name, last_name, department, salary, and age.
INSERT INTO employees (employee_id, first_name, last_name, department, salary, age)
VALUES (7, ‘Sita’, ‘Rao’, ‘Finance’, 55000, 29);
Output:
The table employees now includes:
employee_id | first_name | last_name | department | salary | age |
7 | Sita | Rao | Finance | 55000 | 29 |
Inserting Data into All Columns
If you want to insert data into all columns of the table, you do not need to specify the column names. However, the order of the values must match the order of the columns.
Syntax:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
Example:
Inserting a new record into the employees table without specifying column names:
INSERT INTO employees
VALUES (8, ‘Manish’, ‘Patel’, ‘IT’, 62000, 32);
Output:
The table employees now includes:
employee_id | first_name | last_name | department | salary | age |
8 | Manish | Patel | IT | 62000 | 32 |
Inserting Data into All Columns
You can insert multiple rows in a single INSERT INTO statement by separating the sets of values with commas.
Syntax:
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …),
(value1, value2, …),
…;
Example:
Inserting multiple records into the employees table:
INSERT INTO employees (employee_id, first_name, last_name, department, salary, age)
VALUES (9, ‘Ananya’, ‘Kumar’, ‘Marketing’, 57000, 28),
(10, ‘Ravi’, ‘Sharma’, ‘HR’, 49000, 33),
(11, ‘Deepak’, ‘Jain’, ‘IT’, 66000, 30);
Output:
The table employees now includes:
employee_id | first_name | last_name | department | salary | age |
9 | Ananya | Kumar | Marketing | 57000 | 28 |
10 | Ravi | Sharma | HR | 49000 | 33 |
11 | Deepak | Jain | IT | 66000 | 30 |
Tips to Remember
- Column Order: Ensure the order of values matches the order of columns if column names are not specified.
- Data Types: Ensure the data types of the values match the data types of the columns.
- Constraints: Be aware of any constraints like NOT NULL or UNIQUE that may affect your ability to insert data.
- Use Transactions: When inserting multiple rows, consider using transactions to ensure data integrity.
Power of INSERT INTO
The INSERT INTO statement:
- Adds New Data: Allows you to populate your database with new records.
- Supports Multiple Records: Facilitates the insertion of multiple rows in a single operation.
- Enforces Constraints: Ensures new data adheres to table constraints.
Frequently Asked Questions
Q1: What if I forget to include some columns in the INSERT INTO statement?
A1: If you don’t specify all columns, you must provide values for every column listed. Omitted columns must have default values or allow NULL.
Q2: Can I insert data into a table without specifying column names?
A2: Yes, but you must provide values for every column in the table, and in the correct order.
Q3: What happens if I insert a duplicate value into a column with a UNIQUE constraint?
A3: SQL will return an error if you try to insert a duplicate value into a column with a UNIQUE constraint.
Q4: How can I handle errors during data insertion?
A4: Use transactions to manage errors and roll back changes if something goes wrong. Also, check for constraints and data types to prevent issues.
Q5: Can I use INSERT INTO with a SELECT statement?
A5: Yes, you can insert data from another table using a SELECT statement. This is useful for copying data between tables.
Example:
INSERT INTO employees_backup (employee_id, first_name, last_name, department, salary, age)
SELECT employee_id, first_name, last_name, department, salary, age
FROM employees;
