To enable users to work with data from several different tables when updating data in Excel to SQL Server, the latest release of SQL Spreads now supports working with SQL Server Views.
The support for SQL Server Views makes it possible to fetch data from a single, or even multiple SQL Server tables. The data is shown side by side in Excel like any other table, and end users can update the data and write the changes back from Excel to SQL Server.
Working with Views with data from joined tables
SQL Spreads also supports working with Views that use data from multiple joined tables. When updating data in joined tables, SQL Server restricts the update to only one table in each update.
SQL Spreads will automatically detect the joined tables in a View and set the columns from the first table as the editable columns.
You can specify which table should be editable simply by unchecking the Read-Only checkbox for a column from another base table.
Restrictions in SQL Server when updating data in views
Updating a View is very similar to updating a table. There are a few restrictions to keep in mind when updating Views:
- You cannot update columns in Views that are derived, that are in some way modified in the View’s select statement, like a SUM, CASE or similar.
- You can only insert rows if the View references a single base table.
- You can only delete rows through a View if it references a single base table.
Adding and removing columns in a View when working in SQL Spreads
If you change your View, like adding or removing a column, you will need to fetch the changes in the View into SQL Spreads. You do that by closing and re-opening the SQL Spreads Design mode by clicking the Design mode button in the SQL Spreads ribbon in Excel.
Download free trial today
SQL Spreads 4.1 with support for SQL Server Views is available for download today:
Fantastic new functionality! Thanks!
Thank you Sam, glad you like it!
Será de mucha utilidad.