SQL Server Insert - SQL Server tutorial

The INSERT statement in SQL Server is used to add new rows of data into a table. It is one of the most fundamental SQL operations and allows you to populate or update the database with new information.

Basic Syntax

The general syntax for an INSERT statement is:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

Explanation:

table_name: The name of the table where data will be inserted.

column1, column2, …, columnN: The specific columns into which data is being inserted. These are optional if you are inserting values into all columns in the same order as they appear in the table schema.

VALUES: Specifies the data values to insert.

Example:

Assume you have a table named Employees with the following structure:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
);

An example INSERT statement would look like this:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2024-11-01');

Variants of the INSERT Statement

1. Inserting into All Columns

If you provide values for all columns in the same order as the table’s structure, you can omit the column list:

INSERT INTO Employees
VALUES (2, 'Jane', 'Smith', '2024-11-15');

2. Inserting Multiple Rows

You can insert multiple rows in one INSERT statement by providing multiple VALUES lists:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES 
    (3, 'Alice', 'Johnson', '2024-11-20'),
    (4, 'Bob', 'Brown', '2024-11-25');

3. Inserting Data from Another Table

Use a SELECT statement to insert data into a table from another table:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
SELECT ID, NameFirst, NameLast, StartDate
FROM NewHires;

4. Default and Null Values

If a column has a default value, you can omit it in the INSERT statement, and the default will be applied.
If you explicitly want a column to have a NULL value, include it in the VALUES list as NULL.

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (5, 'Charlie', 'Davis'); -- HireDate will use its default if defined.

5. OUTPUT Clause

The OUTPUT clause can be used to retrieve information about the rows affected by the INSERT:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
OUTPUT Inserted.EmployeeID, Inserted.FirstName
VALUES (6, 'Emma', 'White', '2024-12-01');

Error Handling

Ensure that data types in the VALUES clause match the column data types.
Avoid inserting data into identity columns unless SET IDENTITY_INSERT is enabled for the table.
If the table has constraints (e.g., primary key, foreign key), make sure your data complies with them.

Best Practices

Always specify column names for clarity and to avoid errors when the table structure changes.
Use transactions to ensure data integrity when performing multiple INSERT operations.
This makes the INSERT statement both versatile and robust for managing data in SQL Server.

SQL Server