How to Import Data From Excel to SQL Server – Problem Resolved

Stacy Brown ~ Modified: January 19th, 2017 ~ SQL Server Migration ~ 5 Minutes Reading

Introduction

SQL Server Management Studio (SSMS) has a major property embedded with it i.e. Object Explorer. This property allow users for browsing, choosing & working with several objects in server itself. Therefore, SSMS enable its users with a window i.e. Import Wizard, which one can use for re-creating data from source file to destination file. Now a question arises ‘how to import data from Excel to SQL server?’. As an answer to the same, the following blog will be discussing about a workaround to let SQL users know of the same technique.

Workaround to Import Data From Excel to SQL Server

How to Import Data From Excel to SQL Server? A very common query that SQL user faces in present era. But not to worry for such issue, we have an uncomplicated solution for the same. Go through the following steps, for importing your data from Excel to current SQL server:

Launch SSMS Application: Go to Microsoft SQL Server program group and then search for SQL Server Management Studio option. When you find SSMS option, click on it for launching the application and hence, you will get connected with Database Engine.
Object Explorer Database: Now in the Object Explorer window, right-click on database, select Tasks for launching the Import and Export Wizard. OR

Simply Go to Start menu, move the cursor to All Programs, go to Microsoft SQL Server and then here in Microsoft SQL Server click on Import and Export Data.

Browse Source Excel File: A Choose a Data Source window will be appearing in front of you. From this window, Browse the Excel file from which you want to import data and then click on Next.
Select the Destination: For archiving the imported data, choose a destination for that data. However, you don’t have to define the path, the machine will itself generate its destination path. Therefore, instead of modifying any field of Destination window, just click on Next button.
Choose Table/Query: For selecting tables or views from source data, make use of Specify Table Copy or Query window. Moreover, for mentioning a query to extract data, you can use the same window. However, choose Copy data from one or more tables or views option and then click on Next button.
Select Table & Views: Now a Select Source Tables and Views window panel will appear in front of you from which you will have to choose the table and views that you want to copy to SQL server.

Moreover, there are some more options provided to you in this window for previewing your data or to perform edition.

NOTE: If you do not want to make addition to default data, then you can continue by clicking on Next button.

Preview: First 100 rows of source data can be previewed by this option. After previewing your data, click on OK button to proceed further.
Edit Mappings: For reviewing the mapping of column attributes, you can use Edit Mappings option. The mapping is performed between the source data and the destination data and if want, then you can modify the mapping. However, there are more options embedded with Edit Mappings and they are discussed below:

Drop and re-create destination Table is an option, which is unchecked by default and you can check it for enabling this feature.
If their exist any identity column or source data has some values, which are to be assigned to identity column, then you can check the Edit identity insert option for performing the same.
Create a destination table option is automatically enabled if a new table is being created. In addition, if table exists, then this option is disabled and you can go either for Delete rows in destination table or Append rows to the destination table options.
For reviewing or editting SQL for creating a table in destination source, you can click on Edit SQL button for doing so. In addition, after customizing tables click on OK (Two Times) button for returning back to Select Source Tables & Views window and then click on Next for continuing the process.

Creation of SSIS Package: After clicking on Next from Select Source Tables & Views windows, a Save and Execute Package window will appear. This window provides you with a feature of performing operations related to import and for creating a SSIS package and click on Next to proceed.
Archive SSIS Package: You can archive the SSIS package for reusing it on default path or on any other location as per your need. In addition, you can learn the file name and then click on Next button.
Time to Wind Up: Finally, you will come across Complete the Wizard that will provide you an overview of all the import operation, which you have performed in entire process. Hence, preview all the data and if some changes are required, then click on Back button else start the import process by clicking on Finish.

Following above steps you will easily import your data from excel to SQL Server.

Conclusion

One can wind up with a fact that for importing data from Excel all just needed is Import and Export wizard of SQL server. Therefore, go through the above-mentioned workaround & import data from Excel to SQL server.