Hello and welcome to Excel Jet Consult blog post. In this tutorial, I am going to show you how to import data from Excel to Microsoft SQL Server 2022. Lets get started
Dataset in Excel
The screenshot below shows the two column of data we intend to into into SQL Server using the SSMS. Note, the Excel workbooks is saved using the Excel 97-2003 workbook save as type.
Next, we need to connect to the Server. In this case, I am connected to the SQL Server instance as sa (system admin). The data can be imported into any of the existing databases or you can create a new database based on personal preference or business requirement. In our example, we are going to import in under the SalesData database. Therefore,
- Right-click on the SalesData database and select Tasks and Import Data
- In the Choose a Data Source dialogue box, from the drop-down, select Microsoft Excel
- In the Excel file path, click on Browse and select the Excel workbook. Note, the first row has column names is checked automatically which is correct.
In the Choose a Destination dialogue box, this is where it differs from the previous SQL Server versions. In the previous versions, users are to select SQL Server Native Client 11.0. However, these has been replaced with Microsoft OLE DB Provider for SQL Server to successfully import data from Excel and Access.
- Therefore, from the Destination drop drop-down, select Microsoft OLE DB Provider for SQL Server.
- Provide the authentication as necessary. Note, SalesData database is selected because we initiated the import by right-clicking on the SalesData database.
- Click Next
- In the Select Source Tables and Views dialogue box, click on Next
- Then, click on Finish
The dataset is successfully imported into the SalesData database. However, we need to refresh the database. To do that, right-click on the SalesData database and select Refresh
The screenshot below shows the table in the Sales Database.
See you in the next tutorial