In many businesses, it’s common to use Views to present database tables in a user-friendly way. With SQL Spreads, you can update a View in SQL Server from within Excel. SQL Spreads allows you to work with SQL Server Views, making it easy to display data side by side in Excel, similar to any other table. As a result, end users can effortlessly update the data and directly save their changes back to SQL Server from Excel.
In this blog article, we will guide you step by step on using Excel to update a View in SQL Server.
What is a View in SQL Server?
Generally, a View is a way to add an extra presentation layer in front of a database table. There are a few reasons why a View would be used in a database, namely:
- To narrow down the relevant data for a user by showing only a selection of the database columns
- Show business-friendly column names
- Combine data and columns from several tables and show them in a simple format to the user.
- For security purposes, to limit the user’s access to the underlying base tables by allowing them access to the data through the view.
When non-technical users need to update data in a database, going through the IT department as the middleman can normally be quite cumbersome. A more efficient solution, instead of relying on your IT department, is to use the SQL Spreads Excel Add-in. This Add-in allows you to create controlled Excel sheets linked to your SQL Server database, which can be sent to end users for them to update and save changes directly to SQL Server. Additionally, the ability to use a View in SQL Spreads adds even more value to this approach.
It is much easier to create a View to show only relevant data to non-technical business users where the data from multiple tables is side by side for context. Sometimes, a business user can’t quite understand how a database is structured. And this means that it can be difficult for them to comprehend why their regional data cannot be seen side by side with their customer data in one table, as an example. Consequently, a View sorts out this issue and becomes an excellent way of displaying data for the end users.
SQL Spreads, along with the capability to create Views in SQL Server, enables end users to maintain and update data more efficiently.
How to Update a View in SQL Server from Excel
Use this easy step-by-step guide to setup your Excel document to update data in your SQL Server Views:
- Download and install the SQL Spreads Excel Add-In.
- Open up Excel, and immediately connect to your SQL Server database by entering the name of your SQL Server. Select your authentication method and click OK to connect to your SQL Server.
- A list of databases will appear on the right. Choose the database you are using and select an SQL View to update from Excel.
- From the Columns tab in the SQL Spreads Designer, you can fine-tune the Excel presentation of your View. You have the flexibility to re-arrange, sort and re-name the columns within the View. By default, only the base table in the View is updatable.
- After you have finished fine-tuning your table then update your data in the base table of the View in the spreadsheet. Click the “Save to Database” button to save changes to SQL Server.
Specify which table in the View is editable
Even though SQL Server allows updates in joined tables it also restricts them to one table at a time. This is something that SQL Spreads takes into account when working with Views.
SQL Spreads automatically detects joined tables in a View while making the first table’s columns editable. You can then specify another table to be editable by unchecking “Read-Only” for its columns.
Restrictions in SQL Server when updating data in Views
Updating a View in SQL Server using SQL Spreads is very similar to updating a table in SQL Spreads. Although, there are a few restrictions to keep in mind when updating Views:
- You can’t update columns in Views that are derived – in some way modified in the View’s select statement, like a SUM, CASE or similar.
- Inserting of rows requires a View to reference a single base table.
- Deleting of rows requires a View to reference a single base table.
Conclusion
To sum it all up, using SQL Spreads to link up Excel and SQL Server is like having a secret weapon for businesses that want to get their data act together. Adding SQL Server Views to the mix in SQL Spreads makes handling data even smoother by letting you grab and update info right in Excel.
This guide has shown you step by step how to easily update a View in SQL Server using Excel within minutes, with the SQL Spreads Excel Add-in. Try this out yourself with SQL Spreads and download the free 14-day trial.
FAQs
Can you update a View in SQL?
Yes, updating a View is very similar to updating a table. There are a few restrictions to keep in mind when updating Views:
- When updating Views with data from joined tables, you can only update columns from one table per row.
- You can’t update columns in Views that are derived – in some way modified in the View’s select statement, like a SUM, CASE or similar.
- Deleting of rows requires a View that references a single base table.
- Inserting of rows requires a View to reference a single base table.
How do I update data in a SQL View?
You can update a View directly from Excel using an Excel Add-In with these 4 easy steps:
- Download the SQL Spreads Excel Add-in.
- Connect to your database using the SQL Spreads Designer.
- Select the required View in the SQL Spreads Designer.
- Start editing your data in Excel and saving back changes to the database.
How to use the UPDATE statement to update a View in SQL?
You can also update a View using SQL Server Management Studio using one of these two methods:
- Open the Query editor and write an UPDATE query to update your View like UPDATE [dbo].[v_dim_Account] SET [AccountName] = ‘New Account Name’ WHERE Account = 1010
- Open the Database folder, then open the Views folder, right-click your View and select Edit Top 200 Rows
Why should I use Excel to update a View in SQL Server?
Visualizing and updating data in Excel when it is shown side by side, is much easier than in SQL Server Management Studio. It also gives business users a chance to interact with their data using a familiar tool, namely Excel. A tool like SQL Spreads gets your business users involved in updating their data on SQL Server as well as taking advantage of the Excel presentation benefits.
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.
