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:
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
