How to import data from excel to SQL Server

Good evening friends today I am going to show an example of importing data from excel sheet to SQL Server table. There are two ways of importing data from excel to SQL Server table.

1)Using import and export wizard 
2)Using BIDS (Business Intelligence Development Studio)

1)Using import and export wizard :- To import data from Excel to SQL Server table, we will follow below mentioned steps.

I have created a database named "ETL" and when we right click on the ETL database, the menu shown in the above picture will appear, we will click on tasks menu and it will further expanded into multiple menus.We will select "Import Data" menu as we want to import data from Excel source. Once we click on the "Import Data" menu, below screen will appear.

Now, click on next button as shown in the above picture. It will take us on a different screen as shown in the below picture.

Now we need to specify the source of the data, It means that what kind of source we are using to extract the data. In our case it is excel sheet. Hence we will select "Microsoft Excel" as data source. As soon as we select the "Microsoft Excel" the above screen will be converting in the below shown picture.

Now we need to specify the location of the excel file, we will click on the browse button and locate the file, In my case I have excel sheet with name "EmpData" which has been kept on desktop. Now we will click on next button that will bring a new screen as shown in the below picture.

As the destination is going to be SQL Server itself, hence we are not going to change the destination type.If we want to import data in some other database, we can change the database using database combo list.Now we will click on "Next" button, that will bring the new window as shown in the below image.

We will click on the next button that will bring a new screen, as mentioned in the below image.

In the above picture, there are two columns, one is for source and other one is for destination. In the source column all three sheets of excel are listed, we will select only "Sheet1" as the data is in the sheet1.In destination column, it is showing as "Sheet1$", If we do not change this name, SQL Server create a table with name "Sheet1$", I am going to change this name with a meaningful name. You can see in the below image I have renamed the destination table name with "Employee".

If we click on Edit Mappings button, it will bring a new screen as shown in the below picture.

This screen will show all the details of destination table that is going to be created at run time. Click on "OK" button that will take us on the previous screen. Now we will click on next button that will take us on below screen.

I would like to save this package, hence I have checked the option "Save SSIS package" as soon as I checked the option, It will enable the options (SQL Server, File System), I am going to save this package as a file system. Hence I will select the "File System" option. I have selected the "Do not save sensitive data" as I do not want this package to store the sensitive data in it.

As soon as we click on next button, it will bring us on a different screen, as shown in the below image.

We will provide a name to this package, I have named as "Import Data" to this package. I will click on next button that will bring to next screen as mentioned in the below image.

Clicking on finish button will execute this package that will result into import of data from excel to SQL Server table.

Here is the screen shot of the data contained by excel sheet.

Once the package runs completely, lets check the data into the database in table "Employee".
Below is the result of the select query on Employee table.

We will talk about the other way(BIDS) of importing data from Excel to SQL Server table in my next article.Till then take care and have a nice week ahead.