SQL Insert

SQL Insert Into

This page covers SQL Insert Into statement.

The previous tutorials explored the concepts of databases and tables management.

Now it is time to introduce data management, and more specifically how to fill tables with data.

This tutorial covers the following:

• inserting new records
• inserting multiple records
• inserting specific records

The table used within this page contains 4 columns (id, first name, last name, and age).

The “id” column has the constraints identity, not null, and primary key.

More information regarding keys here and for constraints here.

CREATE TABLE student_info (
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    firstName varchar(50),
    lastName varchar(50),
    age int
    );

id	firstName	lastName	age

Insert new records

In SQL, adding new data occurs through the “insert into” statement.

The keywords are “insert into values”.

When adding new records, we specify the table name, the columns names, as well as the values of the new record.

The following is an example of inserting data of a new student.

INSERT INTO student_info
(firstName, lastName, age)
VALUES
('John', 'Smith', 21);

Please note that the number of columns must match the number of values. In addition, note that the “id” columns is not included. The column contains the identity constraint, which autogenerates student ids. Finally, the “age” column contains integers, therefore age input does not need quotes.

If no errors, the outcome will produce the message “1 row affected“. Let’s check the result with the statement “SELECT * FROM student_info;“.

id	firstName	lastName	age
1	John	Smith	21

Insert multiple records

Adding multiple data records simultaneously follows the same syntax.

Each records must be separated by a comma.

Below is an example of adding two new students to the database.

INSERT INTO student_info
(firstName, lastName, age)
VALUES
('Patrick', 'Casey', 26),
('Sam', 'Adams', 19);

Outcome: “2 rows affected“.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19

Insert specific records

Sometimes, we do not have all the data that we need.

In this instance, SQL allows inserting records into only specific columns.

The following assumes that the age of the student Mike is unknown.

INSERT INTO student_info
(firstName, lastName)
VALUES
('Mike', 'Garcia');

Let’s see the outcome: “SELECT * FROM student_info;“

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL

SQL adds the “age” cell value as NULL. This signifies an empty cell (no value).

---

Next: SQL Update