Working with data inside a database has many advantages to working with data in an Excel spreadsheet. Luckily SQL Server makes it relatively easy to import data from an Excel File into the database.

We will be using the Excel files below:

JobDataSet

JobDesc

Let’s start by opening SSMS (SQL Server Management Studio)

Next, let’s create a database to hold these files. You don’t need to create a new database to import data, but I am building this tutorial as part of a series on SSRS, so I am building a new database for that purpose.

To create a new Database, right click on Databases on the upper left and click New Database…

Now name your new database, we will just accept the defaults

Now go your newly created database, right click, and go to Tasks

From the Tasks sub-menu, select Import Data

The import Wizard will open, simply click Next

Next, select Excel from the drop down

Next, click Browse

Select your file

Make sure First row has column names is checked and click Next

On the next screen select SQL Server Native Client 11.0 (If you don’t have 11.0 – 10.0 should work)

Make sure the database you want is selected and click next

In this example, we are going to use Copy data from one or more tables or views

Make sure to name the table you want to create in SQL Server (red arrow)

If you click Preview you can get a look at what the new table will be loaded with

Click Okay on the preview window and click Next on the Import Wizard

Leave the default Run Immediately checked and click next

Review info on the next window and click Finish

The package will run

Note the blue lettering will let you see how many rows transfer from the Excel file to SQL Server

If you check your database, you will see your tables. (I loaded both spreadsheets in to the database for the upcoming SSRS tutorial)

Finally, run a select * on your new table to see the data you transferred into SQL Server