When trying to import some data from Excel into my test database I over complicated the whole affair, when I could have taken advantage of the Import wizard functionality SSMS offers.
So here are the steps to easily import data from an Excel spreadsheet to your database:
- Select your database, then right click and select Tasks -> Import Data…
- A window will pop out prompting you to select the Data Source:
Be careful with the ‘First row has column name’ tick box, make sure you select the appropriate choice for you.
- Specify where to copy the data to:
4. Click Next to proceed to the Specify Table Copy or Query dialog.
- I have selected the first option from above, so now the Select Source Tables and Views dialog window is prompted. In here we can select the specific sheet from where we wish to copy our data and the destination table (you can either create a new table or select an existing one)
You can click the Edit mappings button to review the column mappings if we’re copying the data to an existing table, we can also use the Preview button to view the first 100 rows of the data in the data source.
- After we’re ok with the settings we have chosen we press the Next button, another dialog window pops out and we can either execute the package we created or we can also save it for future runs.
- Click Next to complete the Import Wizard, and then finish to execute the SSIS package. And then you should see all your data in the tables you’ve selected.