INSERT INTO SQL
Among the multitude of operations, you can perform with SQL, one of the most fundamental and essential is inserting into table SQL, which is accomplished with the SQL INSERT statement. This process involves adding new rows of data to an existing table.
The INSERT INTO SQL command is the backbone of adding data to our databases, and this article is dedicated to learning its application and nuances.
- The SQL INSERT INTO statement is used to add new rows of data to existing tables. It is a fundamental operation in SQL for inserting data into databases.
- The syntax of the SQL INSERT INTO statement includes specifying the table name and the columns to fill. The values to be inserted are provided in parentheses and correspond to the columns mentioned earlier.
- You can add multiple rows using the SQL INSERT INTO statement by including multiple sets of values within the statement. Each set of values is separated by a comma.
- The SQL INSERT INTO SELECT statement allows you to add data into a table based on the result of a SELECT statement. This enables you to add data from one table to another based on specific conditions.
- Ensure that the number of values you're inserting matches the number of columns specified in the INSERT INTO statement. In case of a mismatch, SQL Server will throw an error. Similarly, column constraints such as 'UNIQUE' can prevent duplicate input values from being inserted into specific columns. Violating these constraints will result in an error.
INSERT INTO SQL
Let's first start with the basic syntax of the SQL insert statement.
INSERT INTO SQL Syntax
The SQL INSERT INTO table statement follows a particular syntax. The basic format is as follows:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
In the INSERT INTO SQL syntax above, the "INSERT INTO" phrase is followed by the name of the table to which we want to add data. Then, in parentheses, we list the columns in the table that we want to fill. The "VALUES" keyword follows, and we again use parentheses to list the corresponding values (that we want to insert), for the columns we previously mentioned.
Let's consider an example with a table called 'Students', with columns 'StudentID', 'FirstName', 'LastName', and 'Age'.
Here we have a simple script to create “Students” table:
CREATE TABLE Students ( StudentID INT UNIQUE, FirstName VARCHAR(50), LastName VARCHAR(50), Age INT );
And INSERT INTO SQL command to add data.
INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (1, 'John', 'Doe', 21);
After executing the above INSERT INTO SQL command, a new row will be inserted to the 'Students' table:
Let’s execute the following Select statement to verify:
SELECT * FROM Students
SQL INSERT INTO Multiple Rows
Now, how do we add multiple rows using the INSERT INTO SQL statement? It's quite simple, we just add multiple sets of values.
Look at the syntax for SQL INSERT INTO Multiple Rows:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...), (value4, value5, value6, ...), (value7, value8, value9, ...);
Now, let's add multiple rows to our 'Students' table, run this Insert SQL:
INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (2, 'Jane', 'Doe', 22), (3, 'Jim', 'Smith', 23), (4, 'Jill', 'Johnson', 24);
After executing the above INSERT INTO SQL command, our 'Students' table now looks like
SQL INSERT INTO SELECT
In some cases, we might want to insert data into a table based on the SELECT statement result. This can be accomplished with the INSERT INTO SELECT SQL command (we can also say SQL INSERT From Query), here is its syntax:
INSERT INTO table1 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table2 WHERE condition;
This INSERT INTO SELECT SQL syntax enables us to put data from one table to another based on a particular condition. Here's an example where we insert students from the 'Students' table into an 'Alumni' table if the students 'Age' is greater than or equal to 23.
We first start with an empty 'Alumni' table, here is a simple script to create a 'Alumni' table:
CREATE TABLE Alumni ( StudentID INT UNIQUE, FirstName VARCHAR(50), LastName VARCHAR(50) );
Now run this SQL INSERT INTO a table command:
INSERT INTO Alumni (StudentID, FirstName, LastName) SELECT StudentID, FirstName, LastName FROM Students WHERE Age >= 23;
After executing the above INSERT INTO SQL command, the 'Alumni' table now looks like this:
SQL Copy Table
The INSERT INTO SELECT SQL statement can also be used to copy all data from one table to another. If you want to create a complete copy of a table, you can do so by omitting the WHERE clause:
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1;
For example, if we need to create a complete copy of the 'Students' table in a new table called 'StudentsCopy'. Let’s assume we already have created the “StudentsCopy” table:
INSERT INTO StudentsCopy (StudentID, FirstName, LastName, Age) SELECT StudentID, FirstName, LastName, Age FROM Students;
But what if we don’t have the table created yet to copy the data into (“StudentsCopy” in our case), and we want a new table exactly the same as we are copying the data, without creating that new table manually? In this case, we can use SQL Server SELECT INTO command!
SELECT * INTO StudentsCopy FROM Students;
After executing the above INSERT INTO SQL command (In case of the existing “StudentCopy” table), or SELECT INTO command (when we want to auto-create “StudentCopy” table), the 'StudentsCopy' table will look exactly like the 'Students' table:
There Are Fewer Columns In The Insert Statement - Error
When working with the SQL INSERT statement, it's important to ensure the number of values you're inserting matches the number of columns specified. If the count mismatch, SQL Server will throw an error.
Consider the following example:
INSERT INTO Students (StudentID, FirstName, LastName) VALUES (5, 'Jessie', 'Doe', 25);
This will throw an error because the number of values (4) exceeds the number of columns specified (3). You may encounter the following error.
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
When designing a table in SQL, column constraints are often set to maintain the integrity of the data. These constraints could be rules such as 'NOT NULL', 'UNIQUE', 'CHECK', etc. If we try to INSERT INTO SQL a value that violates these constraints, SQL Server will reject the transaction and throw an error.
For example, if the 'StudentID' column in our 'Students' table has a UNIQUE constraint and we try to insert a duplicate value:
INSERT INTO Students (StudentID, FirstName, LastName, Age) VALUES (1, 'Jessie', 'Doe', 25);
This command will result in an error since 'StudentID' 1 already exists in the 'Students' table.
Violation of UNIQUE KEY constraint 'UQ__Students__32C52A78443F90B6'. Cannot insert duplicate key in object 'dbo.Students'. The duplicate key value is (1).
The SQL INSERT INTO statement is a crucial part of working with SQL Server databases. It is a widely used tool that allows us to populate our tables with data, either row by row or by using the results of a SELECT query. You should now be well-equipped to INSERT INTO table SQL efficiently and effectively.