How To Import Excel Data Into SQL Server 2019

Introduction

In this article, I am going to explain to you how to import Excel sheet data to SQL server database table with examples. Here we will be using SQL Server 2019 or you can use SQL Server 2008 or above. In my current project, I am using this concept for data migration.

This concept is used for transferring data from source (Excel sheet) to destination (SQL Server). We can import a bulk amount of excel data into SQL database without using any coding.

There are two ways to import data from excel to SQL Server database. First, if you want to import all excel file data with header names in a new table. Secondly, if you want to append excel data in exiting table data without header names.

Prerequisites

SQL Server 2019 or you can use SQL server 2008 or above version.

Read my previous articles, using the below links,

• How To Install SQL Server 2019
• How To Install SQL Server Management Studio 2019
• Getting Started With SQL Server 2019

Import All Excel Data With Header Names In a New Table

Step 1

Here, I have created a sample Excel data that contains employees detail data with six columns. 

Step 2

Connect To SQL Server Management Studio 2019 (SSMS) and create a database

Open your SQL Server and use the following script to create the “chittadb” Database.

Create database chittadb

Now, select the script query then press F5 or click on Execute button to execute the above script.

You should see a message, “Command(s) completed successfully.” Means your new database is created.

Database “Chittadb” created.

Step 3

Expand the database where you want to import the data. Here I want to import the data to “Chittadb” database. The database has no table now.

Right click your destination database ->Select Tasks -> Import Data

Step 4

An Import wizard pop up will open "Welcome to SQL Server Import and Export Wizard” and Click Next.

Step 5

• Now, Select Microsoft Excel as your data source from the dropdown list.
• Click the 'Browse' button to select the path to the Excel data file you want to import.
• Select the version of the excel file (2007-2010). Is usually fine for files with a .XLS extension, or use newer files with a .XLSX extension)
• Tick the 'First Row has column names' checkbox, if your excel file contains headers.
• Click next.

Step 6

On the ‘Choose a Destination’ Screen, select destination database” SQL Server Native Client 11.0” from the dropdown, select database “chittadb”, also you can use Windows Authentication or Use SQL Server Authentication; here I am using Windows Authentication and click Next.

Step 7

Select the Radio button “Copy data from one or more tables or views”.

This step will copy the data from the data source, click next and continue.

Step 8

We can specify the table name in the following wizard. So change the destination section name "[dbo].[Sheet1$]" as "[dbo].[Tbl_Employee]" and click Next.

If you want to preview your data, Click the preview button.

Step 9

Save and Run package. Check Run immediately from checkbox and Click Next or Finish button you can get Complete the Wizard page.

Step 10

Click finish.

Step 11

The execution was successful and Click Close button.

Step 12

 Now, check database “Chittadb” and verify.

The following Output contains Id, EmpId, Name, Location, Gender and Designation as the column name and “Tbl_Employee” table also created under database “Chittadb”. Now all excel data is imported to “Tbl_Employee” table.

Conclusion

In this article, we learned how to Import Excel data into SQL Server 2019. Post your valuable feedback in the comments section. I hope this article will be useful.