Understanding the SQL INSERT Statement

Understanding the SQL INSERT Statements: A Comprehensive Guide

This post is part of the series I have been writing called “An Introduction to SQL Server” We have been discussing T-SQL code for data retrieval and now look at data manipulation. In this post we’ll look at how we can get data into our tables using the INSERT statmeent

The SQL INSERT statement is fundamental for adding new data into a table within a database. Whether you’re a database administrator, developer, or data analyst, mastering the INSERT statement is crucial for data manipulation tasks. In this blog, we’ll delve into the mechanics of the INSERT statement, explore its syntax, and provide practical examples to help you understand how it works.

Basics of the SQL INSERT Statement

The INSERT statement allows you to add new rows of data into a table. The basic syntax of the INSERT statement is as follows:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

table_name: The name of the table where you want to insert the data.
(column1, column2, column3, ...): The columns in the table that you want to populate with data.
VALUES (value1, value2, value3, ...): The corresponding values for each column.

Inserting a Single Row

Let’s start with a simple example of inserting a single row into a table named employees.

Assume we have the following table structure:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

first_name VARCHAR(50),

last_name VARCHAR(50),

email VARCHAR(100),

hire_date DATE

);

To insert a new employee record, you would use the following. INSERT statement:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (1, 'John', 'Doe', 'john.doe@example.com', '2023-06-01');

This statement adds a new row to the employees table with the specified values.

Inserting Multiple Rows

You can also insert multiple rows in a single INSERT statement. This is particularly useful for bulk data inserts and can be done as follows:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES

(2, 'Jane', 'Smith', 'jane.smith@example.com', '2023-06-02'),

(3, 'Alice', 'Johnson', 'alice.johnson@example.com', '2023-06-03'),

(4, 'Bob', 'Brown', 'bob.brown@example.com', '2023-06-04');

This statement inserts three new rows into the employees table.

Inserting Data from Another Table

The INSERT INTO SELECT statement is used to insert data from one table into another table. This is useful when you need to copy data from one table to another. The syntax is:

INSERT INTO table_name (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM another_table

WHERE condition;

For example, if you have another table called new_employees with the same structure as employees, you can copy data from new_employees to employees:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

SELECT employee_id, first_name, last_name, email, hire_date

FROM new_employees

WHERE hire_date >= '2023-06-01';

This statement copies rows from new_employees to employees where the hire date is on or after 1 June 2023.

Inserting Data Using SELECT INTO

The SELECT INTO statement is used to create a new table and insert data into it by copying the data from an existing table. This is particularly useful when you need to back up data or create a new table based on the results of a query. The syntax is:

SELECT column1, column2, column3, ...

INTO new_table

FROM existing_table

WHERE condition;

For example, to create a backup of the employees table for employees hired after 1 June 2023, you would use:

SELECT employee_id, first_name, last_name, email, hire_date

INTO employees_backup

FROM employees

WHERE hire_date >= '2023-06-01';

This statement creates a new table employees_backup and inserts rows into it by selecting data from the employees table.

Inserting Default Values

If a column has a default value and you want to use that default value instead of specifying it explicitly, you can omit that column from the INSERT statement:

CREATE TABLE departments (

department_id INT PRIMARY KEY,

department_name VARCHAR(50),

created_at DATE DEFAULT GETDATE()

);

INSERT INTO departments (department_id, department_name)
VALUES (1, ‘HR’);

In this example, the created_at column will be automatically populated with the current date because of the default constraint.

Handling NULL Values

To insert a NULL value into a column, you can explicitly specify NULL in the VALUES clause:

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (5, 'Charlie', 'White', NULL, '2023-06-05');

This statement inserts a row with a NULL value for the email column.

Conclusion

The INSERT statement is a powerful tool for adding data to your SQL databases. By understanding its syntax and various use cases, you can efficiently manage your database records. Whether you’re inserting single rows, multiple rows, copying data from another table, or dealing with default and NULL values, the INSERT statement is essential for effective data manipulation.

Happy querying! If you have any questions or need further clarification on the INSERT statement, feel free to leave a comment over on LinkedIn or reach out to us at gethynellis.com. You can get email updates by subscribing to our Newsletter.

Understanding the SQL INSERT Statement