In this article, you’re going to learn 2 easy ways to perform one of the most useful data management tasks: how to insert data from Excel to SQL Server.
Table of contents
Before I founded SQL Spreads (an Excel Add-In to Import and Update SQL Server data from within Excel), I worked as a Business Intelligence consultant for many years using Microsoft’s BI-tools, such as SQL Server, SSIS, Reporting Services, Excel, etc.
I’ve found that when working on different projects, I tend to snap up a number of great-to-know things that I can re-use over and over again. One of these things that I re-use in almost every project is the ability to copy and paste data from Excel into a table in SQL Server.
It’s a really simple and convenient way to quickly import data into a table via SQL Server Management Studio. For example, populating a new dimension table, adding some test data, or inputting any other data that you need to quickly get into a table in SQL Server.
But what if you want to insert data from Excel to SQL without using Management Studio? What if there was a way to do this directly from Excel? This is where the SQL Spreads Excel Add-In that I’ve been working with over the last few years comes in. It makes your Excel to SQL Server import tasks much easier to do!
In this article, I’m therefore going to explain how to insert data from Excel to SQL Server using these 2 easy methods:
How to import data from Excel to SQL Server – Copy and Paste method
Remember: Always start with copying and pasting a single row of data from Excel to SQL Server. This is to check that there are no mismatches between your data from Excel and the SQL Server table (such as the number of columns) and that your data in Excel validates with the data types in the SQL Server table. See the section “Tips and tricks” below for more details.
How to insert data from Excel to SQL Server with an identity column
The same technique can also be used to copy and paste data into tables that have an auto-incrementing ID column (identity column).
The thing to keep in mind here is to also include an extra left-most blank column in Excel when copying the data from Excel to SQL Server.
Follow these steps to copy and paste the data from Excel to SQL Server using a table with an auto-incrementing ID column:
Copy and paste data from Excel to SQL Server Views
The copy and paste method also works when your Excel to SQL Server import is to a View as opposed to a Table. The only requirement is that the View should only contain data from one table.
In a View in SQL Server that contains data from several joined tables you cannot insert new rows, but you can update the data, as long as you only update columns that originate from the same base table.
Excel to SQL Server import on a remote machine
When working with SQL Server databases on a remote machine, where you connect to the remote machine using a Remote Desktop Connection, you can still use the same copy and paste technique to move the data from your local machine’s Excel to the SQL Server database on your remote machine.
If you are not able to copy and paste the data into your SQL Server when connected using a Remote Desktop Connection, first check that copy and paste is enabled for the Remote Desktop Connection:
If you still cannot copy and paste data between Excel on your local machine and SQL Server on your remote database server, verify with your server administrator that the copy and paste feature is enabled for the Remote Desktop Connection on the server.
Tips when copying and pasting data from Excel to SQL server
Validating your data – start with one row of data
If the data that you copy from your Excel document does not match the data types of the columns in your SQL Server table, the inserting of the data will be canceled and you will get a warning message. This will happen for every row you paste from Excel to SQL Server. If you paste 500 rows from Excel with the wrong number of columns, you will get one warning message for each and every row that you paste.
To avoid this, the trick is to start to copy only a single row of data and paste it into the SQL Server table. If you get a warning message for incorrect data types, you can correct the mismatch and repeat the copy and paste procedure until all your Excel columns fit into the table in SQL Server. When all columns match, select the remaining rows and paste them all into the SQL Server table in one step.
Inserting NULL values from Excel into a SQL Server table
When you have columns in your SQL Server table that allow NULL values, and you want to insert a NULL value into the table, just enter the text NULL into the cell in Excel, and then copy and paste the data from Excel into SQL Server:
The NULL values will be inserted into the table in SQL Server:
Tables with computed columns
For SQL Server tables containing computed columns, you can paste data from Excel into that table simply by leaving the data for the computed column blank in Excel, and then copying and pasting the data from Excel into the SQL Server table.
How to get the column names from the table in SQL Server to Excel
When you prepare the data in Excel for import into an existing SQL Server table, it is useful to have the column headings and a few rows of sample data as a reference in Excel.
There is a technique where you can copy existing data in SQL Server to Excel and include the table column names as header names.
Follow these steps to also include the column names when copying a few rows of data from a SQL Server table into Excel:
Excel to SQL Server performance
Copying and pasting data from Excel to SQL Server is a really simple method to import data from Excel into your SQL Server database. One of the drawbacks is that it is not the fastest method if you need to insert larger amounts of data, such as several hundred thousand rows of data or more.
To get a reference to the performance limits, I have run a few tests on my local i7 machine with 8 GB of RAM with Microsoft Excel and SQL Server installed on the same machine.
I had the following results: copy data in Excel with 10 columns of mixed data types to SQL Server took about 2 seconds for 100 rows, about 30 seconds for 1000 rows, and about 10 minutes for 20,000 rows.
So, I would say that the limit to use the copy and paste feature is around a few thousand up to a few tens of thousands of rows of data. If you need to perform an Excel to SQL Server import with more data, then you should use the SQL Server Import and Export Wizard.
Copy and paste – a quick reference
How to import data from Excel to SQL Server – SQL Spreads method
This method is ideal for users that don’t want to insert data using SQL Server Management Studio.
Install the SQL Spreads Add-In for Excel
You can download a free trial of SQL Spreads from here.
For more details about installation, check out the Installing SQL Spreads section of our Knowledgebase.
Connect to your SQL Server database
Once SQL Spreads is installed, you’ll see that it has been added as a new ribbon tab.
As soon as you select a table, the data in the table is populated in the Excel sheet. You can now see all the data in your SQL Server table and use it in your Excel workbook. The real power with SQL Spreads is the ability to update or add to the data in SQL Server direct from Excel.
Inserting new rows into SQL Server
To import new data into SQL Server, scroll down to the first empty row and either type in your new data or paste a set of rows copied from another Excel workbook:
Once you’ve added or pasted the new rows, click the ‘Save to Database’ button to get the changes written to the table in SQL Server.
Updating existing data in SQL Server
You can also update the prices in the product table directly in Excel, and save the changes back to SQL Server. To do this you simply make the edits in the table in Excel and then click on the ‘Save to Database’ button to get the changes written to the table in SQL Server.
Other tools and techniques
There are some other ways to import an Excel file to a table in SQL Server. Here are some of the other methods.
Summary – insert data from Excel to SQL Server
In this article, we’ve looked at 2 easy ways to insert data from Excel to SQL Server.
If you know how to use SQL Server Management Studio, the copy and paste feature is a great option when you need to quickly and easily import data from Excel to SQL Server. The process is simple and doesn’t require any special knowledge or tools, and can be used in tables with up to a few tens of thousands of rows of data. It can also cater for scenarios such as tables with an auto-incrementing identity key, or if you need to connect to SQL Server on a remote machine using a Remote Desktop Connection.
If you don’t have access to SQL Server Management Studio, you can use the SQL Spreads Excel Add-In to insert data from Excel to SQL. It’s quick and easy to use for non-technical users. For more advanced users, there are some cool features such as lookup columns, pivot options, and data validation which allow you to create robust data management solutions.
Download your free trial of SQL Spreads and get in touch with us if you have any questions.
Worked in the Business Intelligence industry for the last 15+ years.
Founder of SQL Spreads – the data management
Leave a Reply
Dan » 20. Dec, 2018
Thanks for showing the super simple copy/paste functionality. I couldn’t get the Import Wizard to work.
How do I bulk insert from Excel to SQL Server?
Import and Export Wizard.
In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine..
Right-click a database..
Point to Tasks..
Choose to Import Data or Export Data:.
How to bulk insert CSV file in SQL Server?
Run the BULK INSERT utility from SQL Server Management Studio (SSMS). Use the SQL Server Management Studio (SSMS) Import Flat File wizard. Write a program that opens the CSV file, reads its records one-by-one, and calls a SQL INSERT statement to insert the rows into a database table.
How to insert bulk data in SQL Server from Excel using C#?
Bulk Upload Excel to SQL Server in C#.Net.
Create your form for the upload..
Create your DB Structure..
Create your connection string to communicate with the DB. ... .
Create the Form structure to handle the excel reading as well sending the data to the DAO class for the bulk Upload..
How do I bulk insert an XLSX file extension?
It can be done using SQL Server Import and Export Wizard. But if you're familiar with SSIS and don't want to run the SQL Server Import and Export Wizard, create an SSIS package that uses the Excel Source and the SQL Server Destination in the data flow.