Easiest Way To Import Data From Excel To SQL Server

There are many ways to import data from excel to SQL Server. Some are easy while others are difficult.

In this tutorial, you will learn how to import data from an Excel spreadsheet to SQL Server using SQL Server Management Studio only.

But before we learn how to do this, let’s understand why this method is the easiest and the factors that determine what method to use when importing data from a spreadsheet into SQL Server.

Table of Contents show

Factors that determine how to import Excel data to SQL Server

The best way to import data from Excel to SQL Server depends on factors such as:

Tools available
Environment
Skill set

1. Tools Available

There are several tools you can use within the SQL Server universe to import data from Excel. These include:

If you don’t mind converting your Excel data to text or CSV files before importing them to SQL server, you have more options. These include:

Additionally, you can import data from Excel to SQL Server within Excel. This can be achieved using Excel Add-ins such as:

SQL Spreads

Finally, other ETL tools can be used as well. These tools are very efficient and popular in production environments. They include:

Informatica
SAS

2. Environment

Before you decide how best to import data from a spreadsheet to SQL Server, you also need to consider your working environment.

Are you working in a production, testing, or practice environment?
Are you working in a Windows, Mac, or Linux environment?
Are you working in a local, remote, or cloud environment?

Different environments may require different tools.

For instance, if you’re working in a production environment where data imports need to be performed every night, using ETL tools like SSIS or Informatica is ideal.

Additionally, if you work in a cloud environment, these ETL tools are the best fit when importing data from a spreadsheet to a database depending on what cloud environment you work in.

AWS Glue
Azure Data Factory
Google Cloud Dataflow

3. Skillset

If you’re tech-savvy, you can use scripting languages such as Python or C# to import data from a spreadsheet to SQL Server.

However, this could take a considerable amount of time to write and manage. Using scripting languages should come as a last resort when other options do not meet requirements or expectations.

What is the easiest way to import data from Excel to SQL Server?

The simplest way to import data from Excel to SQL Server is to copy data from an Excel sheet and paste it into SQL Server.

What’s easier than copying and pasting?

So, let me show you how to do copy and paste data from Microsoft Excel to SQL Server.

In this tutorial, I will be using the Orders data in the Sample Superstore 2022 dataset.

Step 1:

Create the database SampleSuperStore

CREATE DATABASE SampleSuperStore

Step 2:

Create the table in SQL Server

CREATE TABLE [dbo].[Orders](
	[Row ID] [int] NULL,
	[Order ID] [nvarchar](255) NULL,
	[Order Date] [date] NULL,
	[Ship Date] [date] NULL,
	[Ship Mode] [nvarchar](255) NULL,
	[Customer ID] [nvarchar](255) NULL,
	[Customer Name] [nvarchar](255) NULL,
	[Segment] [nvarchar](255) NULL,
	[Country] [nvarchar](255) NULL,
	[City] [nvarchar](255) NULL,
	[State] [nvarchar](255) NULL,
	[Postal Code] [nvarchar](50) NULL,
	[Region] [nvarchar](255) NULL,
	[Product ID] [nvarchar](255) NULL,
	[Category] [nvarchar](255) NULL,
	[Sub-Category] [nvarchar](255) NULL,
	[Product Name] [nvarchar](255) NULL,
	[Sales] [decimal](18, 4) NULL,
	[Quantity] [int] NULL,
	[Discount] [decimal](18, 2) NULL,
	[Profit] [decimal](18, 4) NULL
)

Step 3:

Right-click the Orders table you just created and select “Edit Top 200 Rows”.

edit top 200 rows sample superstore import excel to sql server

Step 4:

Click the top left corner of the pane to select the entire row.

Step 5:

Copy your excel data and paste it into the pane. The final result should look like this:

What are the drawbacks of this method?

The biggest drawback here is that this method is very slow compared to other methods.

For example, it took around 30 minutes to import 10,195 rows from Excel to SQL Server.

Therefore, I suggest you only use this method for small datasets.

Final thoughts

Copying and pasting data from a spreadsheet to SQL Server using the SQL Server Management Studio is incredibly easy.

I use this method when I need to copy small datasets from Excel to SQL Server.

As a data analyst, you may find yourself having to move data from Excel to SQL Server database often. I recommend you master this essential technique.

Thanks for reading.