How to perform an INSERT in SQL Server

Inserting data into SQL Server is a fundamental operation for business users who need to add new records to their databases. This guide will walk you through the process of using the INSERT statement in SQL Server, providing comprehensive steps and code snippets you can use directly in SQL Server Management Studio (SSMS).

Understanding the SQL INSERT Statement

The INSERT statement in SQL Server is used to add new rows of data to a table. There are various ways to use the INSERT statement, depending on your needs. The basic syntax for the INSERT statement is:

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

VALUES (value1, value2, ...);

Steps to perform an INSERT in SQL Server

1. Identify the Table and Columns to Insert Into: Determine which table and columns you need to insert data into. Ensure you have the necessary permissions to insert data.
2. Write the INSERT Statement: Formulate the INSERT statement with the appropriate columns and values.
3. Execute the Statement: Run the statement in SSMS to add the new records.

Example 1: Inserting a Single Row

Let’s start with a simple example where we insert a single row into the `Employees` table.

INSERT INTO Employees (FirstName, LastName, Department, Salary)

VALUES ('Jane', 'Doe', 'Marketing', 60000);

Example 2: Inserting Multiple Rows

You can also insert multiple rows in a single INSERT statement. Suppose we want to add three new employees at once.

INSERT INTO Employees (FirstName, LastName, Department, Salary)

VALUES

('Alice', 'Johnson', 'IT', 75000),

('Bob', 'Smith', 'Sales', 50000),

('Carol', 'Taylor', 'HR', 55000);

Example 3: Inserting Data from Another Table

Sometimes, you may need to insert data from another table. For instance, if we have a NewHires table and we want to move these records to the Employees table.

INSERT INTO Employees (FirstName, LastName, Department, Salary)

SELECT FirstName, LastName, Department, Salary

FROM NewHires;

Example 4: Inserting Data with Default Values

If your table has columns with default values, you can insert data without specifying these columns. SQL Server will automatically use the default values.

INSERT INTO Employees (FirstName, LastName)

VALUES ('David', 'Brown');

Example 5: Using OUTPUT Clause with INSERT

The OUTPUT clause can be used to return information from each row that is inserted. This can be helpful for auditing or logging purposes.

INSERT INTO Employees (FirstName, LastName, Department, Salary)

OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName

VALUES ('Eve', 'Davis', 'Finance', 62000);

Best Practices for Using INSERT Statements in SQL Server

• Use Explicit Column Lists: Always specify the columns you are inserting into. This makes your SQL statements more readable and less error-prone.
• Validate Data: Ensure that the data being inserted matches the data types and constraints of the columns.
• Handle Nulls Appropriately: Be mindful of columns that do not allow nulls and provide appropriate values.
• Use Transactions for Bulk Inserts: For inserting large amounts of data, use transactions to ensure data integrity.

BEGIN TRANSACTION;

INSERT INTO Employees (FirstName, LastName, Department, Salary)

VALUES

('Frank', 'White', 'Engineering', 80000),

('Grace', 'Kim', 'Operations', 72000);

-- If everything is correct

COMMIT TRANSACTION;

-- If something went wrong

-- ROLLBACK TRANSACTION;

• Check for Existing Data: When inserting data that must be unique, check for existing records to avoid duplicates.

IF NOT EXISTS (SELECT 1 FROM Employees WHERE FirstName = 'Hannah' AND LastName = 'Green')

BEGIN

    INSERT INTO Employees (FirstName, LastName, Department, Salary)

    VALUES ('Hannah', 'Green', 'Logistics', 67000);

END

It’s easier to insert data into SQL Server from Excel

Inserting data in SQL Server is a straightforward process once you understand the syntax and best practices. It is even easier to use the SQL Spreads Excel Add-In. You can get connected to your SQL Server database from within Excel in minutes. Then you can insert new data into SQL Server immediately. Take a look at our blog article How to insert data into SQL Server using Excel or immediately download the trial to see how it works.

For more tips and detailed guides on SQL Server, visit our SQL Spreads blog.