DbSchema Tutorial | SQL INSERT INTO STATEMENT
When diving into the world of databases, one of the first commands you’ll encounter is the SQL INSERT INTO statement. Essential for adding new rows to your tables, it’s a fundamental tool in every data enthusiast’s arsenal. Let’s explore its various forms and understand its significance.
Introduction
In the realm of databases, data creation is as essential as data retrieval. The INSERT INTO statement serves this primary purpose of adding new rows of data into a database table.
Sample Database Table: Users
| UserID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Smith | 30 |
Describing a Table Structure
Before inserting data into a table, it’s crucial to understand the structure, including column names and their respective data types. Both MySQL and PostgreSQL provide functionalities to inspect a table’s structure.
For MySQL:
The DESCRIBE statement is used.
Syntax:
DESCRIBE table_name;
Example:
DESCRIBE Users;
This command will display the columns of the Users table, their data types, and other characteristics.
For PostgreSQL:
The \d command in the psql command-line client is used.
\d table_name;
Example:
\d Users;
Similar to MySQL’s DESCRIBE, this command will reveal the structure of the Users table in PostgreSQL.
Understanding Data Types and Their Formats
When inserting data into a database, it’s crucial to understand the formats expected by different data types, especially if you’re moving between MySQL and PostgreSQL.
| Data Type | MySQL Format | PostgreSQL Format | INSERT INTO Example |
|---|---|---|---|
| String (TEXT, VARCHAR, CHAR, etc.) | Enclosed in single quotes | Enclosed in single quotes | INSERT INTO tableName (columnName) VALUES ('John Doe'); |
| Number (INT, DECIMAL, FLOAT, etc.) | No quotes | No quotes | INSERT INTO tableName (columnName) VALUES (25); |
| Boolean | TRUE or FALSE (or 1 and 0) | TRUE or FALSE | INSERT INTO tableName (columnName) VALUES (TRUE); |
| Datetime & Timestamp | YYYY-MM-DD HH:MM:SS | YYYY-MM-DD HH:MM:SS | INSERT INTO tableName (dateTimeColumn) VALUES ('2023-08-18 12:45:00'); |
Remember, when working with SQL, always ensure your data types match the expected format of your specific database system. Failure to match the right formats can lead to errors or incorrect data being inserted.
INSERT INTO With Column Names and Values
At times, we need a fine-grained control over which columns we insert data into, especially when not all columns are mandatory or when we want to leave some columns to their default values. By specifying column names, we can ensure that the values get inserted into the right columns.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO Users (UserID, FirstName, LastName, Age)
VALUES (3, 'Emily', 'Adams', 27);
Result:
| UserID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Smith | 30 |
| 3 | Emily | Adams | 27 |
Explanation:
The Users table now has a new row with Emily Adams’ details. This method ensures that the values are inserted into the specified columns in the correct order.
INSERT INTO With Values
In scenarios where you have values for all columns and are certain of their order, you can simplify the statement by omitting column names. This method is faster but requires extra caution.
Syntax:
INSERT INTO table_name
VALUES (value1, value2, ...);
Example:
INSERT INTO Users
VALUES (4, 'Mike', 'Brown', 22);
Result:
| UserID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Smith | 30 |
| 3 | Emily | Adams | 27 |
| 4 | Mike | Brown | 22 |
Explanation:
Mike Brown’s details are directly inserted into the Users table. This approach is straightforward but assumes values for all columns in their defined order.
INSERT INTO Using DEFAULT VALUES
When working with tables, certain columns may have default values specified. This can be useful in scenarios where a specific value is common and should be automatically filled in if not provided. By using the DEFAULT VALUES keyword, you can swiftly insert a new row where all the columns take on their respective default values.
Syntax:
INSERT INTO table_name
DEFAULT VALUES;
Consider a modified version of our Users table where there’s a default value for the Age column. Let’s assume that if no age is provided, the default is set to 18.
Example:
Imagine you want to insert a record for a user named ‘Lucy Gray’, but you don’t have her age. In such a case, the database will use the default value for the age column.
INSERT INTO Users (UserID, FirstName, LastName)
VALUES (5, 'Lucy', 'Gray');
Result:
| UserID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Smith | 30 |
| 3 | Emily | Adams | 27 |
| 4 | Mike | Brown | 22 |
| 5 | Lucy | Gray | 18 |
Explanation:
As we didn’t provide an age for Lucy Gray, the database employed the default age value of 18 for her. Thus, our table now holds five records, with Lucy’s age defaulted to 18.
Using SELECT Query in INSERT INTO
This versatile method lets you insert data from one table into another. It’s particularly useful for data migration, backups, or populating summary tables.
1. Copy Specific Rows from a Table
If you wish to copy specific rows based on a condition, you can combine the power of the SELECT statement with INSERT INTO.
Syntax:
INSERT INTO table_name1
SELECT columns FROM table_name2 WHERE condition;
Example:
INSERT INTO OlderUsers
SELECT * FROM Users WHERE Age > 30;
Result:
Only Jane Smith’s details are copied since she meets the condition of being older than 30.
OlderUsers Table After Query:
| UserID | FirstName | LastName | Age |
|---|---|---|---|
| 2 | Jane | Smith | 30 |
Explanation:
This query identifies and copies records of users older than 30 from the Users table to the OlderUsers table.
2. Insert All Columns of a Table
To duplicate all the rows from one table into another, this method is ideal.
Syntax:
INSERT INTO table_name1
SELECT * FROM table_name2;
Example:
INSERT INTO OlderUsers
SELECT * FROM Users;
Result:
All records from the Users table are copied.
OlderUsers Table After Query:
| UserID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Smith | 30 |
| 3 | Emily | Adams | 27 |
| 4 | Mike | Brown | 22 |
Explanation:
Every record from the Users table is duplicated into the OlderUsers table.
3. Inserting Specific Columns of a Table
When you want to copy only certain columns, you can specify those columns explicitly.
Syntax:
INSERT INTO table_name1 (column1, column2, ...)
SELECT column1, column2, ... FROM table_name2 WHERE condition;
Example:
INSERT INTO OlderUsers (FirstName, LastName)
SELECT FirstName, LastName FROM Users WHERE Age > 30;
Result:
Only the names of users over 30 are copied.
OlderUsers Table After Query:
| FirstName | LastName |
|---|---|
| Jane | Smith |
Explanation:
Only the names of users aged above 30 are copied from Users to OlderUsers. Other details like UserID and Age are left out.
Common Mistakes
- Mismatching Column Order: Ensure the order of columns matches with the values you’re providing.
- Duplicate Primary Key: Ensure you’re not inserting a row with a primary key value that already exists.
- Data Type Mismatch: Ensure that data types of the values being inserted match with the column data types.
Frequently Asked Questions
Q: Can I insert multiple rows in a single statement?
A: Yes. You can provide multiple rows of values separated by commas.
INSERT INTO Users (UserID, FirstName, LastName, Age)
VALUES (5, 'Anna', 'Davis', 28),
(6, 'Sam', 'Jones', 32);
Q: Do I always need to use the VALUES keyword?
A: When inserting data directly, yes. But when copying from another table using a SELECT statement, no.
Practice Questions
How would you insert a new user named “Tom Rogers” aged 45 into the Users table without specifying column names?
Insert user “Lucy Gray” with UserID 7 and Age 29 into the Users table by specifying column names.
Copy all users above the age of 30 from the Users table to another table named ‘OlderUsers’.
Write a SQL query to insert the following details into an Employees table: ID - 1001, Name - Robert Smith, JobRole - Manager.
Modify your previous query to insert Name and JobRole only, without specifying an ID.
Given a Customers table and an Orders table, how would you insert the details of all customers living in New York from the Customers table into the Orders table?
What SQL command would you use to insert multiple rows of data into a Products table?
Conclusion:
Mastering the INSERT INTO statement is crucial for anyone working with SQL databases. By following this guide and practicing regularly, you’ll get the hang of it in no time.
Happy querying!
