SQL Spreadsheets online help Prev Page Prev Page
About SQL Spreadsheets
Version history
Contact us
Supported database engines
Using SQL Spreadsheets
Home page
SQL workbench
Command selection
Data sets
Exporting data sets
Exporting to text files
Exporting to Excel
Define column formats
Task settings
DATETIME tag formatting options
Connect to database
Conecting to databases directly without client libraries installed
Connecting to an Oracle database
SQL scripts
SQL tags
Image and file options
Define column formats
Delivery channels
Logging options
Run task
Scheduling a task

SQL tags

You can use tags in your SQL scripts to provide dynamic parameters every time your export task runs. You can load a range of values from a file using the <NUMBERLIST> and <TEXTLIST> tags, or embed single value tags using <NUMBERVALUE>, <TEXTVALUE>, and <DATEVALUE> tags. You can use multiple tags in your SQL script e.g.


File based tags

and <TEXTLIST> tags allow you to replace the tag with a range of values loaded from a file. Both tags need to be appended with an underscore and an identifier, e.g. <NUMBERLIST_IDList>, <TEXTLIST_CustomerNames>.

For e.g. you could write a SQL script like this:
You can then define that <NUMBERLIST_1> should read the values from the file F:\data\IDList.txt on this page.


Similarly, you can choose to read a list of text values from a file using the <TEXTLIST>
tag. For e.g. you could write a SQL script like this:
SELECT * FROM customers WHERE region IN (<TEXTLIST_regions>)  
You can then define that <TEXTLIST_regions> should read the values from the file F:\data\RegionsList.txt. For text values, you do not need to provide the single quote delimiters.


Single value tags

Single value tags read their values from columns in your SQL script. For e.g. you have a table containing a list of customer details, whose primary key is an auto-incremented value in the ID
column. You want to export only the new customers each time. You could then write your SQL script like this:
SELECT ID, Name, Address, ContactNumber FROM customers WHERE ID > <NUMBERVALUE_ID> ORDER BY ID  
You can then define that <NUMBERVALUE_ID> should read from the ID column. Now, every time the script runs, it will use the last ID value it processed in the previous run. In this way, SQL Spreadsheets will only export new customer details. Remember to use the ORDER BY clause, so that your data always follows the same order.


Similarly, you use the <TEXTVALUE>
and <DATEVALUE> tags for text and date values respectively. The tags must be appended with an underscore, followed by an identifier e.g. <TEXTVALUE_1>, <TEXTVALUE_UserName>, <DATEVALUE_2>, <DATEVALUE_LastModifiedDate>.

and <DATEVALUE> tags, you do not need to enclose the tags with single quotes. SQL Spreadsheets will add the quote automatically, so you can write something like this:
SELECT LotNumber, ProductID, ProductDescription FROM products WHERE LotNumber > <TEXTVALUE_1>  
ORDER BY LotNumber