How to Insert Data in SQL Database?

When you start learning SQL, one of the most important things you need to know is how to insert data in SQL database. Adding data to your tables is essential for storing and working with information, whether it’s customer records, sales transactions, or product details.

In this guide, I’ll show you exactly how to insert data into SQL tables, step by step. I will also cover more advanced cases like how to insert multiple records in SQL and how to insert 5 records in SQL using simple and clear examples.

Basic Syntax: Inserting a Single Record

The most basic form of inserting data uses the INSERT INTO statement.

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

Let’s try to insert a record into Customers table in our Northwind database. Here is the example:

INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country)
VALUES ('MSOFT', 'Microsoft', 'Bill Gates', 'Owner', 'Redmond 95', 'Washington', 'WA', '20001', 'USA')

Here’s what INSERT INTO looks like in SQL Server Management Studio:

The query above will insert one single record to the Customers table. You can run the following query to see the record you just created:

SELECT * FROM Customers

Here are the results in SSMS:

How to Insert Multiple Records in SQL

If you want to insert more than one record at a time, you don’t need to write multiple INSERT statements. SQL allows you to insert multiple rows in a single command.

Each set of values is enclosed in parentheses and separated by commas. This is faster and cleaner than writing multiple single-row insert statements.

INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country)
VALUES 
    ('APL', 'Apple Inc.', 'Tim Cook', 'CEO', 'One Apple Park Way', 'Cupertino', 'CA', '95014', 'USA')
  , ('GOOG', 'Google LLC', 'Sundar Pichai', 'CEO', '1600 Amphitheatre Parkway', 'Mountain View', 'CA', '94043', 'USA')
  , ('AMZN', 'Amazon.com, Inc.', 'Andy Jassy', 'CEO', '410 Terry Ave North', 'Seattle', 'WA', '98109', 'USA')

You will see that 3 new records have been created:

How Do You Insert Multiple Rows at Once in SQL?

Rows and records are the same thing when we speak about SQL. We can insert multiple rows to other tables, the same way we did it in the previous example. Let’s try to insert a few records into our Employees table.

Example:

INSERT INTO Employees(EmployeeID, LastName, FirstName, Title)
VALUES
(10, 'Ash', 'Joe', 'Sales Representative' ),
(11, 'Belfort', 'Mike', 'Sales Representative'),
(12, 'Girard', 'Mary', 'Sales Manager')

Important: The number of columns and the data types must match for all rows you insert.

You will probably get the following error when you run they query:

This error happens because you’re trying to manually insert a value into a column that automatically generates values (IDENTITY column), but SQL Server doesn’t allow that by default. When IDENTITY_INSERT is OFF (which it usually is), you must let SQL Server generate the value automatically. To insert your own value, you would need to temporarily enable IDENTITY_INSERT for that table. In normal inserts, simply leave out the IDENTITY column from your INSERT statement. This ensures SQL Server assigns the next number automatically.

Since we don’t want system to automatically assign the number, we have to add the following line before your INSERT INTO query:

SET IDENTITY_INSERT Employees ON

When you run the query again, you will probably see the following result:

How to Insert 5 Records in SQL?

Inserting exactly 5 records works the same way — simply add 5 VALUES rows.

INSERT INTO Employees (EmployeeID, LastName, FirstName, Title)
VALUES
    (13, 'Turner', 'Alice', 'Sales Representative')
  , (14, 'Dawson', 'Robert', 'Sales Representative')
  , (15, 'Nguyen', 'Linda', 'Sales Manager')
  , (16, 'Patel', 'Raj', 'Sales Representative')
  , (17, 'Foster', 'Emma', 'Sales Assistant')

This adds exactly five new employees into the Employees table:

Summary

Now you know how to insert data in SQL database using simple INSERT INTO statements. You’ve also learned:

• How to insert multiple records in SQL using a single command with multiple VALUES.
• How do you insert multiple rows at once in SQL, which makes your queries cleaner and faster.
• How to insert 5 records in SQL, which follows the same method, just specifying exactly five rows.

Inserting data is the foundation of working with SQL databases. Once you’re comfortable with this, you can move on to more advanced topics like UPDATE, DELETE, and bulk imports.

If you missed my previous SQL blog post on UNION in SQL Server, make sure you check it out here.

If you would like to learn more about SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)