Selecting a table or view
When SQL Spreads is connected to SQL Server, the available databases, tables, and views will be shown in the Database Tab in the SQL Spreads Designer.
To load a table or view into Excel, select the table in the Designer:
To load multiple tables into the same Excel document, switch to an empty Worksheet in Excel and select the second table.
Finding your database or table
If you have large amounts of databases and tables in your SQL Servers it can be hard to find a specific table or database.
SQL Spreads has a database and table filtering feature that makes it really easy to find the database and table you are looking for.
To filter the list with databases and tables
- Click the filter icon in the Database tab to show the filter fields:
- Enter a filter for either the databases or the tables to be shown, or both if needed:
- The tree-list with databases and tables will be instantly updated to reflect your filter criteria.
SQL Spreads will remember the last entered filter criteria and will automatically show the filtered list next time you open Excel.
To clear the filter and show all databases and tables
To clear the filter and show all databases and tables, click the filter icon with the red cross:
Preview of 50 rows in the SQL Spreads Designer
When the SQL Spreads Designer is opened, SQL Spreads will preview only the first 50 rows in the table.
You can turn off the Preview and also see the total number of rows in the table at the top in the Columns tab in the SQL Spreads Designer.
Turn off the Preview of 50 rows
To turn off the preview in the SQL Spreads Designer and show all rows, click the Show all rows checkbox.
When you close the Design mode and click Refresh, all rows from the table will be loaded into Excel.
Working with views
Updating data in SQL Server views are in most cases similar to updating data in SQL Server tables.
When updating data in views that fetch their data from joined tables, there is a restriction that you can only update the data from one base table in each update. SQL Spreads will automatically detect the joined tables in a View and set the columns from the first base table to editable and the other columns to Read-Only.
To make the columns from another base table editable, simply uncheck the Read-Only checkbox for a column from the other base table in the Columns tab in the SQL Spreads Designer.
Selecting the Key column(s) for the table
SQL Spreads requires a unique key for each row in the selected table or view. The key can be a single column or a combination of multiple columns. If the key column(s) are set up in the database to be either an Identity column or a Primary Key column, SQL Spreads will automatically detect the key column(s).
You can see which column(s) that are the key column in the Is Key column in the Columns tab in the SQL Spreads Designer:
If your Key column is set up in SQL Server to be an auto-incrementing Identity column, SQL Spreads will fetch new key values for inserted rows and show them in the key column in Excel after you press Save in SQL Spreads.
If the key column(s) are not automatically detected, you will have to manually specify which columns that are the key column(s). When you select a new table where SQL Spreads cannot detect the key column(s), you will be shown a dialog for selecting the key columns.