SQL INSERT Tutorial

The INSERT INTO statement is used to add new records (rows) to a table in a SQL database.

Basic INSERT Syntax

The simplest form of the INSERT INTO statement allows you to specify the table name and the values for each column.

INSERT INTO table_name
(column1, column2, column3, ...)
VALUES
(value1, value2, value3, ...);

Explanation:

INSERT INTO table_name: Specifies the table you want to insert data into.
(column1, column2, column3, ...): Lists the columns you are providing values for. This part is optional if you are providing values for all columns in the table in the correct order.
VALUES (value1, value2, value3, ...): Specifies the values to be inserted. The order of values must correspond to the order of columns listed, or the order of columns in the table if the column list is omitted.

Example: Inserting into a 'Customers' table

Let's assume we have a table named Customers with the following structure:

Column Name	Data Type	Constraints
CustomerID	INT	PRIMARY KEY, AUTO_INCREMENT
FirstName	VARCHAR(50)	NOT NULL
LastName	VARCHAR(50)	NOT NULL
Email	VARCHAR(100)	UNIQUE
JoinDate	DATE	DEFAULT CURRENT_DATE

Scenario 1: Providing values for all columns (except AUTO_INCREMENT)

To insert a new customer, we can use the following statement. Note that we omit CustomerID as it's set to auto-increment, and JoinDate will use its default value.

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@example.com');

After this statement, a new row will be added to the Customers table. The CustomerID will be automatically assigned, and JoinDate will be set to the current date.

Scenario 2: Inserting values for specific columns

If you only want to provide values for a subset of columns, you must explicitly list them.

INSERT INTO Customers (FirstName, LastName)
VALUES ('Jane', 'Smith');

In this case, CustomerID will be auto-incremented, Email will be NULL (if allowed by the table definition, otherwise this might cause an error if Email is NOT NULL without a default), and JoinDate will use its default value.

Inserting Multiple Rows

You can insert multiple rows with a single INSERT INTO statement by providing multiple sets of values.

INSERT INTO Customers (FirstName, LastName, Email)
VALUES
('Alice', 'Williams', 'alice.w@example.com'),
('Bob', 'Johnson', 'bob.j@example.com'),
('Charlie', 'Brown', 'charlie.b@example.com');

This is more efficient than running multiple individual INSERT statements.

Inserting Data from Another Table

You can also insert data into a table based on the results of a SELECT query from another table. The columns in the SELECT list must match the columns in the INSERT INTO list in number and compatible data types.

Let's say we have a table called NewLeads and we want to move some of them to Customers.

INSERT INTO Customers (FirstName, LastName, Email)
SELECT LeadFirstName, LeadLastName, LeadEmail
FROM NewLeads
WHERE LeadStatus = 'Qualified';

This statement will take rows from NewLeads where LeadStatus is 'Qualified' and insert the corresponding first name, last name, and email into the Customers table.

Important Considerations:

Data Types: Ensure the data you are inserting matches the data type of the column.
Constraints: Be mindful of NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints. Violating these will result in an error.
Default Values: If a column has a default value and you don't provide a value for it, the default will be used.
AUTO_INCREMENT/Identity Columns: Typically, you do not need to provide values for these; the database handles them automatically.
NULL Values: You can explicitly insert NULL values if the column allows it.