How to Import Data from Excel to SQL Server

When you compare SQL with Excel in in terms of Data storage capacity and stability, SQL wins hands-down.

But to enter data in SQL manually using queries is a huge pain, especially when all your data is already lying in Excel sheets. We will thus learn to import data from Excel to SQL server.

You have a huge data in your Excel sheet and you would like to have the same data in SQL server as well. Doing it manually is going to be really time-consuming. What if I tell you, this can be done with few easy steps and the data will be imported from Excel to SQL server in no time.

Thus, to import data from Excel to SQL, you can use the SQL Server Import and Export wizard to quickly import your existing Excel data into SQL in no time.

Let’s say you are working in a retail clothing company, dealing in Men’s T-shirts. You get a lot of orders every day. In March 2017, just in one month, you received 12000 orders. You punched in all this data in Excel.

Now, you realise that you need a multi-user application to handle data entry coming in from 10 different users concurrently. So, you decide to build an application with SQL as the backend database.

You have to put in all this data into SQL. If you try to enter it manually using queries, it is extremely time-consuming and too much unnecessary effort. So, you want to import all this data from Excel into SQL.

Data in excel

Here, we have a sample of 15 records from that large data that you have in EXCEL.

Follow the steps below to learn how to Import Data from Excel to SQL Server

how to import data from excel to SQL server

Before you begin the process, go to SQL Management Studio and decide in which Database are you going to import this data into.

Step 1:

Go to Start menu, search for SQL server and then click on SQL server 2016 Import and Export Data on your computer.

SQL server 2016 Import and Export

Step 2:

Now, a dialogue box will open “Choose a Data Source”. Choose Microsoft Excel from source drop down list. Also make sure the version of the excel is correct.

Choose Data Source in Excel

SQL server Import & Export Wizard

Choose Data Source

Step 3:

Choose Destination. From the drop down list, choose SQL Server Native Client 11.0 and then Click on Next.

Choose a Destination

Step 4:

Choose the Database where you want a table to be created. In our case here, we have chosen Funky Tshirts Pvt. Ltd.

Choose Database in Destination

Step 5:

Select “Copy data from one or more tables or views” and then click on NEXT.

SQL Server copy data

Step 6:

Choose the sheet in which your data is present. Click NEXT. You may have multiple sheets in your Excel file, so make sure you choose the correct one.

SQL Server

Step 7:

Now, Select the option “Run immediately” and then click on NEXT.

SQL Server Import and Export Wizard

Step 8:

Verify everything and only then click Finish.

Step 9:

SQL Server Successful Execution

Execution Status.

Thus, we have learnt to import data from Excel to SQL server.

Step 10:

Now we need to ensure that our data in imported correctly into SQL from Excel.

SQL Query

Run a Select * from tablename query and check.

Step 11:

By default, the table name that will be taken by SQL will be the Sheet name of the Excel. You can Renaming the Table After importing. You will not want to keep the table name as ‘Sheet1$’, so rename it using the SQL Management Studio.

SQL Object Explorer

In the SQL Management Studio, point to the table and right-click on the table name.

Then click on Rename and give a new name to the table. We have given the name Sales in this case.

Rename SQL Query

Now run the Select query with the new table name.

How to Export Data from SQL to Excel

If your company is using an application where the backend database is in SQL and multiple users are entering data into the database concurrently, data can go into thousands and lakhs of rows.

To analyze this data in SQL is definitely possible, but requires a lot of SQL skills.

If you’re someone who is great at Excel, but not so much at SQL, the best for you is to export the data from SQL to Excel and then work on it in Excel.

You can use the Charting tools in Excel, what-if analysis and other tools and analyze your data much faster.

There are 2 ways to export data from SQL to Excel.

Do the same process as Importing data from Excel to SQL, simple reverse the Source and the Destination. Earlier, the source was Excel and destination was SQL, now the source will be SQL and the destination will be Excel.

Another possible way is a simple copy paste. First, simple run a select * from tablename query. You will get the output as full data of that table.

SQL to Excel

From the top-right corner, right click and Select All.Then again right click, and select Copy with Headers.Lastly, simple hit paste in Excel.

Founder & CEO at DLTC.co and Blogger at YouTube.com/ExcelRush. Certified Microsoft Office Specialist in Excel. Trained 2000+ working professionals and management students. Strength is training in Excel’s Advanced features & functions, programming using VBA in Excel, also Power BI & SQL. Multifunctional work experience of 11+ years. Dancer at Heart.