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

Output:

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

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:

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:

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).