There are many instances where you might be developing an application, but you want some seed data or business data to be populated into a table in SQL Server. Considering such a scenario, where source data resides in an excel file and you just want to import data from the excel file to your SQL database table. There are many different ways to do it, like writing a script, macro etc. But, there is a simple solution where you could just import data from excel file to SQL database just like a copy paste would work. Let’s get it done step by step.
Have a source data ready
We have here a sample source data in an excel file. It is just a simple 2 column data. As you can see the excel file has 2 worksheets; Sheet1 and Sheet2. Our data here resides in Sheet2.
Have a target database in SQL Server
In the SQL Server, you need to create a database where data would be imported. You can create a table beforehand or on the fly as your data gets imported.
Launch import data wizard
Right click on the target database and the select Tasks -> Import Data. This will bring up the step by step wizard for data import. You might get a start page with some introductions. Just get past it by clicking on Next.
Choose a data source
Choose a source from where you want to import the data. In the list of Data Source, Select Microsoft Excel. Then, browse the excel file having the data for import. You should also be able to select appropriate Excel version. There is an important option called First Row has column names. Check this if the data in the excel file has its first row as column names. As in this example, the first row is the name of columns, i.e. Id and Name. If there is no column name and the file just contains data, uncheck this option. Some column names would be generated automatically which you could easily rename. Click Next to proceed.
Choose destination for data import
Choose a destination for data to be imported into. The destination should already be set to SQL Server Native Client 10.0 depending in which version of SQL server is installed. Provide appropriate Server name for name SQL Server is running. Or you can also open up dropdown and select from there. Specify type of authentication used. Select target database name from database dropdown. Click Next to proceed.
In next step keep Copy data from one or more tables… selected and proceed to next step.
Map Source and destination tables
The next screen would let you map the source and destination data sources. The source column would show all the worksheets in excel file. Check the worksheet which has data. In this case it is Sheet2. At this point the destination is auto populated with something like [dbo].[Sheet1$]. Here Sheet1$ is the name of target table. So change the name to table name you want.
You can also view and change mappings using Edit Mappings option. It opens up a new window with a few options. Creating destination table would automatically create table in the database. Then you can choose to drop and recreate tables if you want. The mappings section lists mapping between the source columns and destination columns. You can change the type, size and null constraint. Or you could also correct the mapping if they are not mapped correctly.
Alternatively, you could also view and edit the SQL query using Edit SQL option. Click Ok when done and proceed to next step.
Complete the import process
The next step will allow you to run import process immediately or save it as an SSIS package for later uses. At this point you can choose to run it now and click on either Next or Finish. Clicking next will take to a summary of the operation that will be performed. Review it and click Finish. Voila!!! Your data must have been imported successfully into the new table.
