People in Business Intelligence, Data Analytics and other data projects often know how to update data in SQL Server — but find it time consuming or frustrating.
We hear from IT and BI teams that they spend time writing scripts, building custom web applications, or even manually updating data in SQL Server to fulfill requests from other team members.
SQL Spreads is an Excel Add-in that solves this problem by providing you and your team a secure, reliable, and fast way for end users to update SQL Server data directly from within Excel – every business user’s favorite data tool! With its designated Designer and Editor roles, SQL Spreads safeguards your data and ensures that business users can update only what you permit, securing your Excel and SQL Server data management.
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 feel safe working with data using the program.
That’s why using Excel also for updating and managing data in SQL Server makes so much sense. End users already know the tool, and can be up and running in minutes.
In this step-by-step guide, I’ll walk you through how you can use the SQL Spreads Excel Add-in to efficiently and accurately update data in an SQL Server table from Excel. To follow along, simply download the free trial version for access to all SQL Spreads functionality.
You will learn
- How to easily connect an Excel document to SQL Server and start updating your data in the database
- How to fine-tune how your SQL Server table can be updated from within Excel
- How to share the document with your non-technical end users
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 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.
Enter the name or URL to your SQL Server, select your desired authentication method: Windows, SQL Server or Azure AD authentication and click OK. You are now connected to your SQL Server from within 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.
Find your database and select the SQL Server table to update from within Excel.
Note: The Design 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.
Step 4: Start updating your SQL table – from within Excel
Your SQL Server table is now loaded into Excel and you can immediately start making updates to the SQL Server data from within Excel. When you press the Save to Database button, you will be asked to confirm the changes, before they are saved back to your SQL Server table.
But if you want to share the Excel file with others, you most likely want to fine-tune how your table is presented in Excel first.
Step 5: Fine-tune your setup
Fine-tuning how your SQL Server tables can be updated from Excel is done using the SQL Spreads Designer.
Here you can do things like:
- Select which columns to show or hide, and which columns should be editable.
- Rearrange and rename columns.
- Automatically lookup key columns, and show drop-down lists to select a readable text instead of a key value.
- Add row filters to select which database rows to load from SQL Server into the Excel spreadsheet.
- Turn on Change Tracking to insert the date and time when a row is changed, as well as the user making the change.
- And much more…
Step 6: Share your new SQL Server-connected Excel document
When you are finished fine-tuning your table, close the Design mode and save the Excel document.
You can now share your SQL Server-connected Excel document like any other Excel file. All the settings will follow the document, and other users (whom you have given access) can use your Excel file to update the SQL tables from Excel.The separate Designer and Editor roles safeguard your setup and only let the users to update what you have allowed them to.
SQL Spreads makes it easy
In Business Intelligence, Data Analytics, and other data projects, spending precious time building custom solutions to update and maintain data is both inefficient and frustrating. With SQL Spreads, you can — literally in minutes — let your end users update data in structured SQL Server tables themselves, using Excel and the SQL Spreads Excel Add-in.
With SQL Spreads:
- Users work in the well-known, user-friendly Excel interface
- Minimal to zero training is needed
- No coding is required
- Data is automatically validated
- Key lookups show texts in drop-down lists
SQL Spreads’ separate Designer and Editor roles safeguard the Excel documents and give you exact controls over how the SQL Server tables can be updated.
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 whom.
- 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
Maybe one of the biggest benefits of SQL Spreads is its ease of use. You can get started in minutes and have a new data management solution up and running in just a couple of hours.
On top of that, the familiarity of the Excel environment for business users lowers the learning curve and minimizes the need for training.
And when it comes to maintaining the solution, you can simply open your Excel file and update the setup. There’s no need to start a new project or involve a developer.
Let your non-technical users securely update their SQL Server data in Excel
— and take the task off your plate
The benefits of SQL Spreads go beyond just letting end users across your organization take control of their data — it’s also a win for you. You can delegate routine data tasks and focus on higher-level responsibilities.
But the advantages don’t stop there. SQL Spreads can deliver far-reaching value across your business or enterprise, with immediate time savings and improved efficiency.
- Fast and simple setup means you’re up and running in no time.
- Data owners gain easy access to centralized data through the familiar Excel interface.
- No more manual imports – you can eliminate the hassle of using SSIS packages or maintaining complex VBA scripts.
SQL Spreads replaces time-consuming processes with a fast, reliable, and user-friendly data management solution.
Start Using SQL Spreads Today – and simplify your SQL data updates
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?
Absolutely, multiple SQL tables can be updated from the same Excel workbook. In the SQL Spreads Excel Add-in, one SQL table goes into one Excel sheet.
How do I add data from Excel to an existing SQL table?
The easiest way is by using an Excel Add-in, like SQL Spreads, which lets you connect Excel to SQL Server. Then you can copy and paste Excel data that can be saved into your SQL table.
How do I track who changed a specific record in SQL if the edits happen via Excel?
You can do that by enabling Change tracking in the SQL Spreads Excel Add-in. Once enabled, timestamps and user names for each updated row are automatically recorded.
Can multiple users edit the same SQL table from Excel at once?
Yes, as long as it is managed. An Add-in like SQL Spreads flags conflicting edits and tracks changes made by each user.
