• The Excel and SQL Server experts blog
  • Use Excel to update data in SQL Server Views

    Johannes Åkesson - Nov 28, 2016

    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.
    Use Excel to update SQL Server Views from joined tables
    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.
    Select which table to update

    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.
    Turn Design mode on and off

    Download free trial today

    SQL Spreads 4.1 with support for SQL Server Views is available for download today:

    Comments (3)

    Sam » 05. Dec, 2016

    Fantastic new functionality! Thanks!

    Noel » 26. May, 2017

    Será de mucha utilidad.

Use Excel to update your data in SQL Server?

Try the SQL Spreads data management solution to update and manage your SQL Server data from within Excel.