• SQL Spreads Blog

  • How to insert data from Excel to SQL Server

    May 4th, 2017

    Background
    Other tools and teqniques
    How to insert data from Excel to SQL Server – step-by-step instructions
    Insert data into a SQL Server table with an identity column
    Copy and paste data from Excel to SQL Server Views
    Tips when copying data from Excel to SQL server
    Excel to SQL Server on a remote machine
    Excel to SQL Server performance
    Requirements and limitations
    Summary – inserting data from Excel to SQL Server
    Excel to SQL Server – a quick reference

    Background

    Before I founded SQL Spreads (an Excel Add-In for using Excel to update data in SQL Server), 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 possibility to copy and paste data from Excel into a table in SQL Server.

    It’s a really simple and convenient way to quickly insert data into a table in SQL Server—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.

    In this article, I will go through the basics of how to copy and paste data from Excel into SQL Server using easy step-by-step instructions. I will also cover a few special cases and techniques that are great to know.

    If you do a lot of updating of data from Excel to SQL Server, or if you have users who love Excel but are not used to the database tools, take a look at the SQL Spreads Excel Add-In that I’ve been working with over the last few years since I left my job as a BI-consultant.

    Other tools and techniques

    Moving data from Excel to SQL Server is an ongoing task in the business world that relies on Microsoft’s SQL Server databases. There are a number of tools and techniques available to accomplish this task, each with different benefits, and with a different level of complexity.

    I will briefly go through a few of the different tools, as well as their benefits and use cases. The most common tools available to import an Excel file to a table in SQL Server are:

    • SQL Server Import Wizard – a wizard-based import tool inside SQL Server Management Studio. It can be used for one-time imports when you have an Excel document with data that you need to import into a table in SQL Server. The pros include flexibility and lots of settings to finetune the import. The biggest drawback is that you need to run through a dozen Wizard dialogs with lots of settings each time you need to import the data. More info about the Import Wizard is available here.
    • SSIS – this is the oil tanker for moving data between different sources – you can do almost any task you like, but you will need to put in lots of time to get started, and it will take still more time to maintain and change the solution down the line. The pros include good versatility and plenty of available features; the main con is the time you will have to put in to learn the tool. More info about SSIS is available here.
    • The BCP utility – a command line-based tool that offers a huge number of settings – if you are a coder, this is the tool to use. More info about the BCP utility is available here.

    These are great tools to move the data, especially if you have the demand to import data from an Excel file to SQL Server on a day-to-day or weekly basis. The drawback is that the tools require a number of steps to set up and will take some time to configure.

    The nice thing about the “copy and paste” technique described below is that it is really quick and it works out of the box, using standard Excel and SQL Server Management Studio. It can also handle tables with identity columns, and you can use it for a few thousand rows of data with reasonable load times.

    How to insert data from Excel to SQL Server – step-by-step instructions

    1. Open SQL Server Management Studio and connect to your SQL Server database.
    2. Expand the Databases and the Tables folders for the table where you would like to insert your data from Excel.
    3. Right-click the table and select the fourth option – Edit Top 200 Rows.
    4. The data will be loaded and you will see the first 200 rows of data in the table.
    5. Switch to Excel and select the rows and columns to insert from Excel to SQL Server.
      Right-click the selected cells and select Copy.
    6. Switch back to SQL Server Management Studio and scroll down to the last row at the bottom and locate the row with a star in the left-most column.
    7. Right click the star in the column header and select Paste.
    8. Your data from Excel is now pasted into your table in SQL Server!

    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 a SQL Server table 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:

    1. Open SQL Server Management Studio and connect to your SQL Server database.
    2. Expand the Databases and the Tables folders for the table where you would like to paste the Excel data.
    3. Right-click the table name and select Edit Top 200 Rows, the fourth option from the top.
    4. This will bring up a grid with the first 200 rows of data in the table.
    5. Switch to Excel and select the rows and columns to copy. Do not include the header row.
      Now, also remember to include an extra blank left-most column in your selection.

      Then, right-click the selected cells and select Copy.
    6. Switch back to SQL Server Management Studio, and select the tab with the 200 rows from your table.
      Go to the last row at the bottom and locate the row with a star in the left-most column.
    7. Right-click on the star and select Paste.
    8. Your data from Excel is now pasted into your table in SQL Server, and SQL Server will automatically create the values in the ID/key column for you:

    Copy and paste data from Excel to SQL Server Views

    The presented copy and paste method to insert data from Excel to SQL Server also work great when you need to insert data into a SQL Server View, as long as the View only contains 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.

    Tips when copying 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 preparing the data in Excel that later should be copied to a table in SQL Server, it is convenient to have the database column names and a few rows of example 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:

    1. In SQL Server Management Studio, locate your database and expand the Tables folder.
    2. Right-click your table name and select the third option – Select Top 1000 rows.
    3. Select the rows to copy to Excel by holding down the CTRL button and clicking the row numbers on the left side.
    4. When your rows are selected, right-click one row and select the Copy with Headers option:
    5. Go to Excel and paste the data into a cell. The headers from the table in SQL Server will now be added as the first row:

    Excel to SQL Server 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:

    1. Open the Remote Desktop Connection.
    2. Click the Show Options…
    3. Select the Local Resources tab, and then check that the Clipboard property is checked:

    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.

    Excel to SQL Server performance

    Copying and pasting data from Excel to SQL Server is a really simple method to insert 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.

    Requirements and limitations

    To copy and paste data from Excel to SQL Server you will need Microsoft Excel and SQL Server Management Studio installed. You can download the free version of SQL Server Management studio here.

    Copying data from Excel to SQL Server works great up to a few thousand rows of data with reasonable load times.

    The copy and paste method can be used both for tables with business keys and for tables with auto-incrementing ID keys.

    One thing to keep in mind is that the copy and paste method is only for inserting new data. If you need to update existing data in a table in SQL Server, you will need to use either an external tool such as SQL Spreads, or insert the data into a temporary table and then write a small SQL query that will update your table with the inserted data.

    Summary – inserting data from Excel to SQL Server

    Inserting data from Excel to SQL Server using the copy and paste feature is a really simple and convenient way to quickly get the data into the database without having to run and configure any special tool. The copy and paste feature can be used in scenarios with up to a few tens of thousands of rows of data.

    The copy and paste method also work great for tables with an auto-incrementing identity key – just remember to add an extra blank column left of the data columns as described above.

    It can also be used when working with Excel on a local machine and SQL Server on a remote machine using a Remote Desktop Connection.

    Excel to SQL Server – a quick reference:

    • First, copy the data from Excel, and then paste it into the SQL Server table using the Database > Table > Edit top 200 rows menu option.
    • Always start by copying and pasting a single row of data to validate the data types.
    • For SQL Server tables with an identity column, add an extra blank left-most column before copying from Excel.
    • Copy and paste from Excel to SQL Server can be used to insert up to a few tens of thousands of rows of data.
    • To get the SQL Server column headers into Excel, right-click the table in SQL Server and select Copy with headers.
    • Don’t forget that the technique also works great over Remote Desktop Connections!

Leave a Reply


No comments yet. Be the first!