You can use Actions in exTables to transfer data from exTables to a Microsoft SQL Server of your choosing. This Action can either be added as a button within the exTables editing area or even be automatically executed on save.
Here are the steps required to create an action that transfers data:
1. Open the table you want to publish in exTables. Then go to the Table Designer and click Edit Actions.
2. Add a new task by selecting the Publish Data to External Database. Give the new task a name, a description and select the appropriate attributes.
3. Either select a connection or create a new connection to the destination database using the Connections drop-down.
- To create a new connection go to the Connection drop-down and select Edit Connections.
- Fill in the Connection Information and Authentication and click Generate Connection String. Then click Test Connection to confirm that the connection works.
Authentication can be either; Windows Authentication which uses the credential of the user running exTables, or Username and Password which uses SQL Login as authentication.
After configuring the connection, click Save Connection.
Note: Make sure that your new connection is selected.
4. When you have selected a connection, select the destination table for the data. You will need to create the destination table outside of exTables.
After selecting the destination table, you will need to configure which columns in the source table map to which columns in the destination table. This is done under "Column Mapping".
If you want to skip a column, select "No mapping" for that column.
5. You can add a filter to the source data under "Filter". The syntax there is standard T-SQL. For example: WHERE AddressType = 'Premise Address'
To make sure your filter works as expected use the Preview button.
5. If you want to execute a script on the destination database before or after the transfer, click the Pre/post execution button and add your script. This can for example be helpful to truncate data before transferring data, or to execute a stored procedure after the transfer.
6. Click Run action if you want to run your action immediately.
7. Click Save Tasks to save your action.