In the realm of SQL, learning how to insert data into tables is a fundamental skill for anyone aspiring to manipulate databases effectively.
The SQL INSERT INTO statement is the command you need for this task, allowing you to add new rows of data into a table. Let’s delve deeper into this key statement with a plethora of examples and illustrative code blocks.
What is INSERT INTO Statement?
The SQL INSERT INTO statement facilitates inserting new records into a database. You can add either a single row containing values for each column or multiple rows at once. Here’s the basic syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
In the syntax above, table_name is where you want to insert the data. (column1, column2, column3, …) specifies the columns for the data, and VALUES (value1, value2, value3, …) represents the data values to be inserted.
It’s also possible to use INSERT INTO without specifying column names. However, you should ensure the values are in the same order as the columns in the table. The syntax for this approach is
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Examples to Deepen Your Understanding
1) Inserting Single Row
Let’s assume we have a table named Employees, which includes the following columns: EmployeeID, FirstName, LastName, Email, and PhoneNumber.
CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
Email VARCHAR(100),
PhoneNumber VARCHAR(15)
);
To insert data into this table, we can run the following SQL command
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, PhoneNumber)
VALUES (1, 'John', 'Doe', '[email protected]', '123-456-7890');
The table would now look like this
| EmployeeID | FirstName | LastName | Email | PhoneNumber |
|------------|-----------|----------|-----------------------|--------------|
| 1 | John | Doe | [email protected] | 123-456-7890 |
2) Inserting Multiple Rows
We can also insert multiple rows at once
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, PhoneNumber)
VALUES
(2, 'Jane', 'Doe', '[email protected]', '098-765-4321'),
(3, 'Jim', 'Beam', '[email protected]', '567-890-1234');
After running this statement, our Employees table would look like
| EmployeeID | FirstName | LastName | Email | PhoneNumber |
|------------|-----------|----------|-----------------------|--------------|
| 1 | John | Doe | [email protected] | 123-456-7890 |
| 2 | Jane | Doe | [email protected] | 098-765-4321 |
| 3 | Jim | Beam | [email protected] | 567-890-1234 |
3) Inserting Data without Specifying Columns
Consider a table Students with three columns: StudentID, FirstName, and LastName. To insert data into the Students table without specifying the column names, we ensure the order of values matches the order of the columns.
INSERT INTO Students
VALUES (2, 'Jane', 'Smith');
The Students table now looks like
| StudentID | FirstName | LastName |
|-----------|-----------|----------|
| 1 | John | Doe |
| 2 | Jane | Smith |
4) Inserting Data into Specific Columns
If we only have data for some columns, we can still use INSERT INTO by specifying the known columns
INSERT INTO Students (StudentID, FirstName)
VALUES (3, 'Emma');
This command inserts a new record with StudentID 3 and FirstName ‘Emma’. The LastName field will remain NULL for this record (assuming the table schema allows NULLs). The table now looks like
| StudentID | FirstName | LastName |
|-----------|-----------|----------|
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Emma | NULL |
5) Inserting Data from Another Table
The INSERT INTO statement can also insert data into a table selected from another table. Let’s say we want to copy all students from Students to GraduatedStudents
INSERT INTO GraduatedStudents (StudentID, FirstName, LastName)
SELECT StudentID, FirstName, LastName FROM Students;
This command will copy all records from Students to GraduatedStudents.
Conclusion
Mastering the SQL INSERT INTO statement is crucial for effective database manipulation. By providing the means to add new records to our database tables, this statement is undoubtedly a vital command in SQL. Continue practicing, and soon enough, you’ll be confidently managing your databases like a pro.
