How to Upload Data From Excel to Sql Server

In this commodity, y'all're going to learn two easy means to perform one of the nigh useful data management tasks: how to insert data from Excel to SQL Server.

Table of contents

• 1. Background
• 2. How to import information from Excel to SQL Server – Re-create and Paste method
  • a. Footstep-by-step instructions
  • b. How to insert information from Excel to SQL Server with an identity cavalcade
  • c. Copy and paste information from Excel to SQL Server Views
  • d. Excel to SQL Server import on a remote machine
  • eastward. Tips when copying and pasting data from Excel to SQL server
  • f. Excel to SQL Server performance
  • g. Copy and paste – a quick reference
• iii. How to import data from Excel to SQL Server – SQL Spreads method
  • a. Install the SQL Spreads Add together-In for Excel
  • b. Connect to your database and insert data from Excel
  • c. Inserting new rows into SQL Server
  • d. Updating existing information in SQL Server
• 4. Other tools and techniques
• 5. Summary – insert data from Excel to SQL Server

Background

Before I founded SQL Spreads (an Excel Add-In to Import and Update SQL Server information from within Excel), I worked as a Business Intelligence consultant for many years using Microsoft'southward BI-tools, such every bit SQL Server, SSIS, Reporting Services, Excel, etc.

I've found that when working on unlike projects, I tend to snap up a number of not bad-to-know things that I tin can re-use over and over again. I of these things that I re-use in virtually every project is the ability to copy and paste data from Excel into a table in SQL Server.

Information technology's a really simple and user-friendly manner to apace import data into a table via SQL Server Management Studio.  For example, populating a new dimension tabular array, adding some test data, or inputting any other information that you demand to quickly become into a table in SQL Server.

But what if yous desire to insert data from Excel to SQL without using Direction Studio?  What if there was a way to do this direct from Excel?  This is where the SQL Spreads Excel Add together-In that I've been working with over the terminal few years comes in.  Information technology 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 ii piece of cake methods:

• Re-create and paste from Excel to SQL tables via SQL Server Direction Studio
• Insert directly from Excel to SQL tables using SQL Spreads

How to import data from Excel to SQL Server – Copy and Paste method

Footstep-past-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 tabular array and select the fourth option – Edit Height 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 Re-create.
   
6. Switch back to SQL Server Management Studio and scroll down to the last row at the lesser 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. You accept now completed your SQL Server import, and your data from Excel is now in a table in SQL Server!

Remember: E'er 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 tabular array. Encounter 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 tin can as well be used to copy and paste data into tables that accept an auto-incrementing ID column (identity column).

The matter to keep in mind hither is to besides include an actress left-almost blank cavalcade in Excel when copying the information from Excel to SQL Server.

Follow these steps to copy and paste the data from Excel to SQL Server using a tabular array with an auto-incrementing ID cavalcade:

1. Open SQL Server Management Studio and connect to your SQL Server database.
2. Expand the Databases and the Tables folders for the tabular array where you would like to paste the Excel data.
   
3. Right-click the table name and select Edit Top 200 Rows, the quaternary option from the top.
   
4. This will bring upward a filigree 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, too retrieve to include an extra blank left-most column in your choice.
   
   Then, correct-click the selected cells and select Copy.
6. Switch back to SQL Server Direction 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 cavalcade.
   
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 information from Excel to SQL Server Views

The re-create and paste method also works when your Excel to SQL Server import is to a View as opposed to a Table.  The merely requirement is that the View should only incorporate data from one table.

In a View in SQL Server that contains data from several joined tables you cannot insert new rows, just you can update the information, every bit 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 motorcar, where you lot connect to the remote machine using a Remote Desktop Connexion, you tin however 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 continued using a Remote Desktop Connection, first check that copy and paste is enabled for the Remote Desktop Connexion:

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

If yous still cannot re-create and paste data between Excel on your local automobile and SQL Server on your remote database server, verify with your server administrator that the re-create and paste characteristic is enabled for the Remote Desktop Connexion on the server.

Tips when copying and pasting data from Excel to SQL server

Validating your data – start with 1 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 information will be canceled and you lot will get a warning bulletin. This will happen for every row y'all paste from Excel to SQL Server. If you paste 500 rows from Excel with the wrong number of columns, yous will get one warning bulletin for each and every row that you paste.

To avoid this, the trick is to showtime to copy only a single row of data and paste it into the SQL Server tabular array. If you become a warning message for wrong information types, y'all can correct the mismatch and echo the re-create and paste procedure until all your Excel columns fit into the tabular array in SQL Server. When all columns match, select the remaining rows and paste them all into the SQL Server tabular array in one step.

Inserting Nada values from Excel into a SQL Server tabular array

When you lot have columns in your SQL Server table that allow Goose egg values, and y'all want to insert a Nix value into the table, only enter the text NULL into the cell in Excel, and and so 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 past leaving the data for the computed column blank in Excel, and and then copying and pasting the information from Excel into the SQL Server table.

How to get the column names from the table in SQL Server to Excel

When you fix the data in Excel for import into an existing SQL Server tabular array, information technology is useful to take the column headings and a few rows of sample information as a reference in Excel.

There is a technique where yous can copy existing data in SQL Server to Excel and include the tabular array column names as header names.

Follow these steps to as well include the column names when copying a few rows of data from a SQL Server table into Excel:

1. In SQL Server Direction 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 downwards the CTRL push button and clicking the row numbers on the left side.
4. When your rows are selected, correct-click one row and select the Copy with Headers option:
   
5. Go to Excel and paste the data into a jail cell. The headers from the tabular array in SQL Server will now exist added as the starting time row:
   

Excel to SQL Server operation

Copying and pasting data from Excel to SQL Server is a really unproblematic 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 demand to insert larger amounts of data , such as several hundred m rows of data or more.

To go 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 aforementioned machine.

I had the following results: copy information in Excel with 10 columns of mixed data types to SQL Server took well-nigh 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 re-create and paste feature is around a few thousand upward to a few tens of thousands of rows of data.  If yous need to perform an Excel to SQL Server import with more information, then you lot should apply the SQL Server Import and Export Wizard.

Copy and paste – a quick reference

• Outset, copy the information from Excel, and then paste it into the SQL Server table using the Database > Table > Edit summit 200 rows menu pick.
• Always start past copying and pasting a single row of data to validate the data types.
• For SQL Server tables with an identity column, add together an extra blank left-near column earlier copying from Excel.
• Re-create and paste from Excel to SQL Server can exist used to insert up to a few tens of thousands of rows of data.
• To get the SQL Server cavalcade headers into Excel, right-click the table in SQL Server and select Copy with headers.
• Don't forget that the technique also works groovy over Remote Desktop Connections!

How to import data from Excel to SQL Server – SQL Spreads method

This method is platonic for users that don't desire to insert data using SQL Server Management Studio.

Install the SQL Spreads Add-In for Excel

You tin download a complimentary trial of SQL Spreads from here.

1. Run the SQLSpreadsSetup.exe file and follow the instructions.
2. Restart Excel and take the Add-In confirmation.
3. You will find SQL Spreads in the tab bill of fare in Excel:
   

For more details well-nigh installation, check out the Installing SQL Spreads department 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.

1. Click on SQL Spreads and and so click the Pattern Mode button
   
2. In the SQL Spreads Designer console on the right side, click the Edit push button to open the SQL Server connection dialog.
   
3. Enter the proper noun of your SQL Server into the SQL Server name field:
   
4. Select if you should connect using your Windows-login (Windows Authentication) or enter a user name and password (SQL Server Hallmark). Windows authentication is the more secure of the 2 options.
   
5. Click OK. SQL Spreads will effort to connect to the database. If the connection is successful, your databases will show upwards in the SQL Spreads Designer panel.
   
6. Now that we've created the connexion from Excel to SQL Server, we can select which table of data we want to use in Excel. In the SQL Spreads Designer, click on the database and then select your table.
   

As soon as you lot select a tabular array, the information in the table is populated in the Excel sheet.  You can now see all the data in your SQL Server table and apply it in your Excel workbook. The existent power with SQL Spreads is the ability to update or add together to the information in SQL Server directly from Excel.

Inserting new rows into SQL Server

To import new data into SQL Server, scroll down to the beginning 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' push to get the changes written to the table in SQL Server.

Updating existing data in SQL Server

Yous tin can also update the prices in the product table directly in Excel, and salve the changes dorsum to SQL Server.  To do this you lot simply make the edits in the tabular array in Excel and then click on the 'Save to Database' push button to get the changes written to the table in SQL Server.

Other tools and techniques

There are some other means to import an Excel file to a table in SQL Server. Here are some of the other methods.

• SQL Server Import Magician – a wizard-based import tool inside SQL Server Management Studio. Ideal for one-fourth dimension imports when you take an Excel document 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 y'all need to run through a dozen Wizard dialogs with lots of settings each fourth dimension you need to import the data. More than info about the SQL Server Import Magician is available here.
• SSIS – this is the oil tanker for moving data between unlike sources. You can practise almost any task you similar, but yous will need to put in lots of time to get started, and it will have withal more fourth dimension to maintain and change the solution downward the line. The pros include skillful versatility and plenty of available features; the master con is the time you volition have to put in to learn the tool. More info most SSIS is available here.
• The BCP utility – a control line-based tool that offers a huge number of settings – if you are a coder, this is the tool to apply. More info about the BCP utility is available hither.

Summary – insert data from Excel to SQL Server

In this commodity, we've looked at two easy means to insert data from Excel to SQL Server.

If you know how to apply SQL Server Direction Studio, the copy and paste feature is a great option when y'all need to rapidly and hands import information from Excel to SQL Server. The process is simple and doesn't require whatever special noesis or tools, and can be used in tables with upward to a few tens of thousands of rows of data.  Information technology tin can also cater for scenarios such as tables with an auto-incrementing identity cardinal, or if you lot demand to connect to SQL Server on a remote machine using a Remote Desktop Connection.

If you don't have access to SQL Server Direction Studio, y'all can use the SQL Spreads Excel Add-In to insert data from Excel to SQL.  It'due south quick and piece of cake to employ for non-technical users.  For more advanced users, there are some cool features such as lookup columns, pivot options, and data validation which allow yous to create robust data management solutions.

Download your complimentary trial of SQL Spreads and get in touch with us if you have whatever questions.

Johannes Åkesson

Worked in the Business Intelligence manufacture for the last 15+ years.

Founder of SQL Spreads – the data management
solution to import, update and manage
SQL Server data from within Excel.

allentoonow.blogspot.com

Source: https://sqlspreads.com/blog/how-to-insert-data-in-excel-to-sql-server/

Share this post