We are proud to announce the release of SQL Spreads 2014 with the completely new Table Wizard. The Table Wizard makes it extremely simple to create an Excel document which can be used to Update, Insert or Delete data in a SQL Server table.
The Wizard contains five screen:
- First select your Database and and Table
- Second, select the Worksheet and Cell where the table data should be inserted
- Third select which columns in the table to include
- Fourth, set up Texts to show as Drop Down Lists in Excel for foreign key columns
- Lastly do a few settings and then click Finish
When clicking Finish, SQL Spreads will load the data from SQL Server into Excel.
You now have an Excel document ready to share with others to let them Update, Insert or Delete data in SQL Server direclty from Excel.
After running the Wizard you can configure every detail of importing and exporting data between SQL Server and Excel. The Wizard will automatically create and configure the following elements in the Excel document:
- An import of table data from SQL Server into the selected worksheet in Excel
- A mapping between the columns in Excel to the Fields in the table in SQL Server
- For any detected Foreign Key relation in the table, the Wizard will setup Integrity Checks with Drop Down Lists in the columns Excel
I have a problem when I import a table without identity column. The table itself has 2 primary keys but no identity because it is a SAP database in MSSQL. Maybe you have the answer to my question?
By the way it’s a good application.
Greetings from El Salvador.
Hi Jose,
Thanks for your feedback!
Currently SQL Spreads requires an ID column with an Identity key for updating a table from Excel. I have had a few requests regarding this and I am looking into adding the use of non-identity columns as key columns.
The workaround for this is to create an intermediate table were SQL Spreads stores the data from the Excel spreadsheet, and then use the Post-Save Query feature (see http://bit.ly/1JMBsAj for details) in SQL Spreads to transfer your changes to the SAP table.
The steps to implement the workaround are:
If you have further question or need help setting up the workaround, please feel free to contact me directly at johannes.akesson@obnex.com.
From release 3.1.0 SQL Spreads supports multiple primary key columns, with no requirement for an identity.