How to Update an SQL Table from Excel: Complete Guide

Written by Johannes Åkesson
Reviewed by Johannes Åkesson
Aug 06, 2024
8 min read

There’s an easier way to update SQL tables

Many teams turn to Excel for updating SQL tables because it’s familiar for business users. However, team members in charge of data management may fear losing control of the database. This makes it crucial to find the right tool for connecting Excel and SQL Server.

In this guide, you’ll learn how SQL Spreads – an Excel Add-In – is just that tool, by giving you a simple yet secure way to update your SQL Server tables. You’ll see how designated Designer and Editor roles safeguards data quality and makes sure that business users can only update what you have permitted, streamlining your Excel and SQL data workflows.

A DimCustomer table in Excel as an example of how a BI team can import data from Excel into SQL Server
This DimCustomer table in Excel is an example of how a BI team can import data from Excel into SQL Server

Why end users love working in Excel

You know it, we know it: end users love working in Excel. They know the tool, are familiar with it, and are free to do what they want.

That’s the heart of the much-loved Excel application, but also the start of problems for the people taking care of the data. The freedom to add cells and enter “whatever-you-like” values causes huge problems when trying to store and summarize the data in a structured way.

Updating or collecting “not-available-in-our-systems” data from colleagues is often done by mailing out some Excel file or putting a spreadsheet on a file share.

When users update data in an Excel spreadsheet that should be saved or updated in an SQL table, problems like these usually occur:

  • Cells in the spreadsheet can contain invalid data types.
  • There will be problems when users change the layout of the sheet.
  • Difficulties to keep track of previous versions of the Excel spreadsheet.
  • Hard to track who has changed a specific value in a sheet.
  • Troublesome to extract Excel data (with a tool such as SSIS).
  • There can be a delay of several hours between when a user enters the figures and when they appear in the database.

SQL Spreads makes it easy

In finance, IT and other fields, structured data is a vital part of the operations, and constantly dealing with the above problems is not an option. Instead of spending precious time troubleshooting or fixing data issues, you can — literally in minutes — let your end-users update data in structured SQL tables themselves, using Excel, with the SQL Spreads add-in. 

With SQL Spreads: 

  • No coding experience or extensive training is necessary
  • Users work with a familiar and user-friendly Excel interface
  • Data in SQL Server tables can be directly updated from Excel 
  • Data is automatically validated when users enter their figures through SQL Spreads
  • Data from other Microsoft Excel documents can be pasted directly into the SQL Server connected documents.

Users are authenticated using their Windows Login, SQL Server or Azure AD authentication and can only work with the Excel documents for which they are authorized. Different permission roles ensure that you maintain control over the configuration, and business users can only edit data as you allow it.

Here’s our step-by-step guide, walking you through how you can use SQL Spreads to efficiently and accurately update an SQL Server database from Excel.

In this guide, I will show you: 

  1. How to easily bring in your SQL Server tables into Excel for easy updating/management
  2. How to share the document with your end users
  3. How to keep track of data quality

Make your end users happy
– let them make SQL updates in Excel without adding to your workload.

Get the Free Trial

How to Update an SQL Table from Excel: Step-by-step

To set up an Excel document to work with the data in an SQL Server table, follow these few simple steps:

Step 1: Download and install the Excel Add-In

Download and install the SQL Spreads Excel Add-In. You can get started with a free 14-day trial if you want to see it in action before committing.

Step 2: Connect to your SQL Server Database

Go to the SQL Spreads tab in Excel and select the Open Designer button.

Click on the Open Designer button in the SQL Spreads ribbon to connect to your SQL Server database
Select your desired authentication method: Windows, SQL Server or Azure AD authentication and SQL Server Name and click on OK. You are now connected to your SQL Server database from within Excel.

Select one of three authentication options to configure connection to SQL Server to be able to update a SQL table from Excel

Note: SQL Spreads relies on Microsoft SQL Server security mechanisms and all of your data is stored on your machine and servers.

Step 3: Select your SQL Server table to update

A list of databases will appear on the right in the SQL Spreads Designer. Designer mode keeps you in charge of the entire configuration, so that the end user – in Editor mode – is only able to make edits within the limits of your configuration.

Choose the database you are using and select an SQL table to update from Excel.

Update existing data in SQL Server by selecting SQL Server table

Step 4: Fine-tune your SQL Server table

From the Columns tab you can fine-tune how your table is presented in Excel. You can select the columns you want to update, rearrange them into the order you prefer, and change their names if desired.

Control your update of existing data in SQL Server with these Designer settings of Show and Read-Only.

Step 5: Update your SQL table from Excel

When you are finished fine-tuning your table, go to the spreadsheet and start updating the data from SQL Server. When you press the Save to Database button, the changes will be saved back to your SQL Server table.

Update an existing value in the SQL Server table and save the changes back to the database.

There are several other great benefits of the SQL Spreads Designer to easily connect an Excel spreadsheet to a table in SQL Server. For example, you can:

  • Set which columns are editable and which are “read-only”
  • Select which rows in the database are loaded into the Excel spreadsheet
  • Enable Change Tracking and the application will then insert the date and time when a row is changed, as well as the user making the change.
  • Show drop-down lists where the user can select a readable text instead of a key value for columns relating to other tables.

Share your Excel document:

Let your non-technical users update and manage the SQL Server data

After you exit the Design mode you can share your Excel document like any other Excel file. All the settings will follow the document and other users can use your Excel file to update the SQL tables from Excel.

But maybe one of the biggest benefits of SQL Spreads is its ease of use. And the benefits are not only for administrators but also for authorized users throughout your business or enterprise. Non-technical users can use SQL Server-connected Excel documents that you create and share with them. The result will be an accurate and effective collaboration with safeguards including built-in conflict detection.

High data quality, automatic key lookups, and validated data only from authorized users:
SQL Spreads keeps your data safe, without complicating input.

Assured Data Quality

To get the highest possible quality of data, SQL Spreads uses several methods to guarantee the validity of the entered data:

  • When figures are entered, they are validated against the data types of the database columns, and the user receives immediate feedback.
  • Each changed row is tracked in the database to see when a row was changed and by who.
  • A built-in conflict detection system enables safe and easy collaboration.
  • When sharing the document with others, they can be given a Data Editor role to disable the Design mode to protect the Excel sheet set up that you’ve created.

You can read about more features here and learn how they maintain high data quality for your database – paramount for anyone handling SQL data. 

Automatic Lookup of key values from other tables

Databases contain relations, and a table with keys relating to other tables can be hard to update manually.

When updating a SQL Server table from within Excel, SQL Spreads can lookup those key values in other tables and show drop-down lists where the user can select a readable text instead of a keys value. When the changes are saved to the database, the looked up key will be saved to the database.

Familiar and User-friendly Excel Interface

The data in SQL Server tables can be directly updated from Excel. Users are authenticated using their Windows Login, SQL Server or Azure AD authentication and can only work with the Excel documents for which they are authorized.

Data is automatically validated when users enter their figures through SQL Spreads. And data from other Microsoft Excel documents can be pasted directly into the SQL Server connected documents.

A Low-Stress Solution with High Value to Your Organization

With SQL Spreads, you can:

  • Use Excel to work with data in SQL Server tables.
  • Let non-technical users work with the SQL Server data.
  • Ensure that the entered data is valid.

But more far-reaching benefits can be offered to your business or enterprise by using SQL Spreads. You will immediately see time savings across the board.

  1. First, the setup is really fast and simple.
  2. Second, when end-users enter data, SQL Spreads will guide them through the right way to enter the data.
  3. Third, data owners will have the advantage of being able to easily access centralized data through Excel.
  4. Fourth, you can put an end to struggling with importing Excel data using SSIS or maintaining VBA scripts.
  5. Lastly, no more troubleshooting and correcting problems created by users altering the spreadsheet.

Those time-consuming processes and frustrations are replaced by SQL Spreads with fast, reliable data management.

Try SQL Spreads First-Hand to Take Control of your SQL Server Data Management

The best way to understand just how easy SQL Spreads can make your life is by seeing it in action. Our free SQL Spreads trial gives you access to all the features for 14 days.

Still not sure? Take a look at our library of demo videos, showing you how easy it is to create an Excel document and update the SQL table from Excel using the SQL Spreads add-in.

Editors note: This blog post was originally published for a previous version of SQL Spreads and has been completely revamped and updated for accuracy and comprehensiveness.

FAQs

Is it possible to update multiple SQL tables from a single Excel workbook?

plusminus

Absolutely, separate worksheets within the same Excel workbook can point to a different SQL table. Just ensure each worksheet is properly configured to connect to and update the correct table.

How do I add data from Excel to an existing SQL table?

plusminus

The easiest way is by using an Excel add-in, like SQL Spreads, which lets you connect Excel to SQL Server so you can update the SQL table right from Excel.

How do I track who changed a specific record in SQL if the edits happen via Excel?

plusminus

Enable auditing or change tracking on the SQL table. Some add-ins can automatically record timestamps and usernames for each updated row. SQL Spreads, for instance, inserts user details into tracking columns when a row is changed.

Can multiple users edit the same SQL table from Excel at once?

plusminus

Yes, as long as it is managed. An add-in like SQL Spreads flags conflicting edits and tracks changes made by each user.


Try SQL Spreads for free

Try SQL Spreads during 14 days for free. The free trial is the fully
functional, time-limited only product.
Try Excel Add-In for Free download-image
stars-image
14-days Free Trial. No credit card required.