|
Introduction |
|
Using SQL Spreadsheets |
|
|
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
The <NUMBERLIST>
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:
|
|
SELECT
* FROM sysobjects WHERE ID IN (<NUMBERLIST_1>)
|
|
|
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>.
For the <TEXTVALUE>
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>
|
|
|
|