How to Upload Data From Excel to Sql Server Table

In this article, you're going to learn 2 piece of cake ways to perform ane of the near useful data direction tasks: how to insert data from Excel to SQL Server.

Table of contents

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

Background

Before I founded SQL Spreads (an Excel Add together-In to Import and Update SQL Server information 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 establish that when working on different projects, I tend to snap upward a number of cracking-to-know things that I can re-use over and once again. One of these things that I re-employ in near every project is the power to copy and paste information from Excel into a table in SQL Server.

Information technology'south a really simple and convenient way to quickly import data into a table via SQL Server Management Studio.  For instance, populating a new dimension table, adding some exam information, or inputting any other data that you need to rapidly get into a table in SQL Server.

Only what if y'all desire to insert data from Excel to SQL without using Management Studio?  What if there was a manner to exercise this direct 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 commodity, I'm therefore going to explicate how to insert data from Excel to SQL Server using these 2 like shooting fish in a barrel methods:

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

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

Pace-by-footstep Instructions

1. Open SQL Server Direction 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 run across 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.
   Correct-click the selected cells and select Copy.
   
6. Switch back to SQL Server Management Studio and scroll down to the concluding 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. Yous have 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 in that location are no mismatches betwixt 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. Meet the section "Tips and tricks" below for more details.

How to insert information from Excel to SQL Server with an identity column

The same technique can likewise be used to re-create 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 re-create and paste the data from Excel to SQL Server using a table with an auto-incrementing ID cavalcade:

1. Open up SQL Server Management Studio and connect to your SQL Server database.
2. Aggrandize the Databases and the Tables folders for the table where y'all would similar to paste the Excel data.
   
3. Right-click the tabular array name and select Edit Superlative 200 Rows, the fourth selection from the summit.
   
4. This volition bring up a grid with the first 200 rows of information in the table.
   
5. Switch to Excel and select the rows and columns to copy. Practise not include the header row.
   At present, also call back to include an extra bare left-most column in your selection.
   
   Then, right-click the selected cells and select Copy.
6. Switch dorsum to SQL Server Management Studio, and select the tab with the 200 rows from your table.
   Go to the final row at the bottom and locate the row with a star in the left-about 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/central column for you:
   

Copy and paste data from Excel to SQL Server Views

The copy and paste method likewise works when your Excel to SQL Server import is to a View equally opposed to a Tabular array.  The only requirement is that the View should only incorporate data from one tabular array.

In a View in SQL Server that contains data from several joined tables y'all cannot insert new rows, simply you tin can update the data, equally long equally you only update columns that originate from the aforementioned base tabular array.

Excel to SQL Server import on a remote automobile

When working with SQL Server databases on a remote machine, where you connect to the remote machine using a Remote Desktop Connection, you tin can still use the same copy and paste technique to move the data from your local auto'south Excel to the SQL Server database on your remote auto.

If yous are non able to copy and paste the data into your SQL Server when connected using a Remote Desktop Connexion, first check that copy and paste is enabled for the Remote Desktop Connectedness:

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

If you yet cannot copy and paste information betwixt Excel on your local car 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 Connection on the server.

Tips when copying and pasting information from Excel to SQL server

Validating your information – start with one row of data

If the information that you copy from your Excel document does non friction match the data types of the columns in your SQL Server table, the inserting of the data will exist canceled and you volition get a warning message. This volition happen for every row you lot paste from Excel to SQL Server. If you lot paste 500 rows from Excel with the incorrect number of columns, you will get ane warning message for each and every row that y'all paste.

To avoid this, the trick is to start to copy only a unmarried row of data and paste it into the SQL Server table. If y'all go a warning bulletin for incorrect data types, you tin correct the mismatch and repeat the copy 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 table in i footstep.

Inserting Aught values from Excel into a SQL Server table

When you accept columns in your SQL Server table that allow Goose egg values, and you desire to insert a NULL value into the table, just enter the text Goose egg into the cell in Excel, and then copy and paste the data from Excel into SQL Server:

The Nil values will be inserted into the table in SQL Server:

Tables with computed columns

For SQL Server tables containing computed columns, yous can paste data from Excel into that table only by leaving the data for the computed column blank in Excel, and and so copying and pasting the data from Excel into the SQL Server tabular array.

How to get the column names from the tabular array in SQL Server to Excel

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

In that location is a technique where you tin copy existing data in SQL Server to Excel and include the table cavalcade 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. Correct-click your table name and select the third option – Select Height grand rows.
   
3. Select the rows to copy to Excel past holding down the CTRL push and clicking the row numbers on the left side.
4. When your rows are selected, correct-click i row and select the Copy with Headers option:
   
5. Go to Excel and paste the information into a cell. The headers from the tabular array in SQL Server volition now be added every bit the beginning row:
   

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. Ane of the drawbacks is that it is not the fastest method if you lot 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 accept run a few tests on my local i7 automobile with 8 GB of RAM with Microsoft Excel and SQL Server installed on the same machine.

I had the following results: copy information in Excel with 10 columns of mixed data types to SQL Server took near 2 seconds for 100 rows, about 30 seconds for 1000 rows, and about 10 minutes for xx,000 rows.

So, I would say that the limit to apply the copy and paste characteristic is effectually a few thousand up to a few tens of thousands of rows of data.  If y'all need to perform an Excel to SQL Server import with more than information, then you should use the SQL Server Import and Export Sorcerer.

Re-create and paste – a quick reference

• Get-go, copy the data from Excel, and then paste it into the SQL Server table using the Database > Table > Edit superlative 200 rows carte option.
• Always start by copying and pasting a unmarried row of data to validate the information types.
• For SQL Server tables with an identity column, add together an extra blank left-most cavalcade earlier copying from Excel.
• Copy and paste from Excel to SQL Server tin 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!

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 costless trial of SQL Spreads from hither.

1. Run the SQLSpreadsSetup.exe file and follow the instructions.
2. Restart Excel and have the Add together-In confirmation.
3. Yous will find SQL Spreads in the tab menu in Excel:
   

For more details about installation, check out the Installing SQL Spreads department of our Knowledgebase.

Connect to your SQL Server database

Once SQL Spreads is installed, you lot'll see that it has been added every bit a new ribbon tab.

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

Equally soon as you select a table, the data in the table is populated in the Excel canvas.  You can now meet all the data in your SQL Server table and use it in your Excel workbook. The existent ability with SQL Spreads is the power to update or add together to the data in SQL Server directly from Excel.

Inserting new rows into SQL Server

To import new data into SQL Server, scroll downward to the first empty row and either type in your new data or paste a ready of rows copied from another Excel workbook:

Once you've added or pasted the new rows, click the 'Save to Database' push button to get the changes written to the table in SQL Server.

Updating existing data in SQL Server

You can as well update the prices in the product table directly in Excel, and save the changes back to SQL Server.  To practise this you lot 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 tabular array in SQL Server.

Other tools and techniques

In that location are some other ways to import an Excel file to a table in SQL Server. Here are some of the other methods.

• SQL Server Import Wizard – a magician-based import tool within SQL Server Direction Studio. Ideal for i-time imports when y'all have an Excel document that y'all demand to import into a tabular array in SQL Server. The pros include flexibility and lots of settings to finetune the import. The biggest drawback is that yous need to run through a dozen Wizard dialogs with lots of settings each time you demand to import the information. More info about the SQL Server Import Wizard is available hither.
• SSIS – this is the oil tanker for moving data between dissimilar sources. You lot can exercise almost any chore you similar, but you volition need to put in lots of time to go started, and it will take still more time to maintain and alter the solution downwards the line. The pros include good versatility and enough of available features; the master con is the time you will have to put in to learn the tool. More than info about SSIS is bachelor here.
• The BCP utility – a command line-based tool that offers a huge number of settings – if yous are a coder, this is the tool to employ. More info most the BCP utility is available hither.

Summary – insert data from Excel to SQL Server

In this article, we've looked at 2 easy ways to insert information from Excel to SQL Server.

If you know how to apply SQL Server Direction Studio, the copy and paste feature is a great selection when you need to quickly and easily import data from Excel to SQL Server. The procedure is simple and doesn't require any special noesis or tools, and can be used in tables with up to a few tens of thousands of rows of data.  It tin can besides cater for scenarios such every bit tables with an car-incrementing identity fundamental, or if you need to connect to SQL Server on a remote automobile using a Remote Desktop Connection.

If you don't take admission to SQL Server Management Studio, you can use the SQL Spreads Excel Add-In to insert information from Excel to SQL.  Information technology's quick and piece of cake to use for non-technical users.  For more advanced users, there are some cool features such as lookup columns, pivot options, and information validation which permit you lot to create robust data management solutions.

Download your gratuitous trial of SQL Spreads and get in touch with us if you take any questions.

Johannes Åkesson

Worked in the Business Intelligence industry for the final fifteen+ years.

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

stewartconsed.blogspot.com

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

Share this post