Example SQL Statements
The following are examples of SQL statements that may be used in the Statements parameter for the Execute SQL action:
INSERT INTO Result SELECT * FROM Source1
Used with the ‘MakeTable Source1 Result’ option, this statement inserts all the rows from the Source1 table into the Result table.
INSERT INTO Result SELECT * FROM Source1 WHERE NAME LIKE 'A%'
Used with the ‘MakeTable Source1 Result’ option, this statement inserts into the Result table rows from the Source1 table where the NAME column has a value beginning with ‘A’.
INSERT INTO Result SELECT * FROM Source1 WHERE String1 AND String2
Used with the ‘MakeTable Source1 Result’ option, this statement inserts into the Result table rows from the Source1 table where the expressions stored in String1 and String2 are satisfied.
UPDATE Result SET Used = 'Y' WHERE UsageCount > 0
Used with the ‘CopyRows Source1 Result’ option, this statement updates the Result table to set the USED column to ‘Y’ where the USAGECOUNT column has a value greater than zero.
DELETE FROM Result WHERE TYPE = 'PROG'
Used with the ‘CopyRows Source1 Result’ option, this statement removes rows from the Result table where the TYPE column has the value PROG.
CREATE TABLE Result AS SELECT DISTINCT NAME FROM Source1 WHERE TYPE = 'TCOD'
Used with no options set, this statement inserts NAME values from the Source1 table into the Result table for distinct rows from the Source1 table where the TYPE column has the value ‘TCOD’. It can be used to create a result table of unique transaction codes.
ALTER TABLE Result DROP COLUMN ACCOUNT
This statement removes the ACCOUNT column from the ‘Result’ table.
ALTER TABLE Result ADD DESCRIPTION nvarchar(30)
This statement adds a text column named DESCRIPTION to the ‘Result’ table.
ALTER TABLE Result ADD DESCRIPTION nvarchar(30)
INSERT INTO Result SELECT Source1.NAME, Source1.TYPE, Source2.DESCRIPTION FROM Source1 LEFT JOIN Source2 ON Source1.NAME = Source2.NAME
Used with the ‘MakeTable Source1 Result’ option, the first statement adds a text column named DESCRIPTION to the Result table. The second statement performs a left join to populate the Result table with all the rows from the Source1 table, and any descriptions from the Source2 table where the NAME columns match.
ALTER TABLE Result ADD DESCRIPTION nvarchar(30)
INSERT INTO Result SELECT Source1.NAME, Source1.TYPE, Source2.DESCRIPTION FROM Source2 LEFT JOIN Source1 ON Source2.NAME = Source1.NAME
Used with the ‘MakeTable Source1 Result’ option, the first statement adds a text column named DESCRIPTION to the Result table. The second statement performs a left join to populate the Result table with rows from the Source2 table where the value in the NAME column has a matching entry in the Source1 table.
ALTER TABLE Result ADD DESCRIPTION nvarchar(30)
INSERT INTO Result SELECT Source1.NAME, Source1.TYPE, Source2.DESCRIPTION FROM Source1 INNER JOIN Source2 ON Source1.NAME = Source2.NAME
Used with the ‘MakeTable Source1 Result’ option, the first statement adds a text column named DESCRIPTION to the Result table. The second statement performs an inner join to populate the Result table with only those rows from the Source1 and Source2 tables where the NAME columns match.
At the time of writing, SQLite does not support right joins.
UPDATE Result SET SELNAME = 'String1', LOW = 'String2'
Used with the ‘CopyRows Source1 Result’ option, this statement updates the Result table to set the SELNAME column to the value of the String1 parameter, and the LOW column to the value of the String2 parameter.
UPDATE Result SET Description = (SELECT PTEXT FROM Source1, Result WHERE PTEXT != '' AND Source1.FOUND_NAME = Result.ObjectName AND Source1.FOUND_TYPE = Result.ObjectType)
Used with the ‘CopyRows Source2 Result’ option, this statement augments the Result table with description data, using the PTEXT column from the Source1 table. The WHERE clause identifies how to look up the appropriate rows in the Source1 table.
UPDATE Result SET PGMA = (SELECT s2.OBJ_NAME FROM Source2 s2 WHERE s2.PARENT_TYPE = 'TCOD' AND s2.OBJ_TYPE = 'PROG')
Used with the ‘CopyRows Source1 Result’ option, this statement updates the Result table to set the PGMA column to a matching value from the OBJ_NAME column in the Source2 table. ‘s2’ is used as an alias for ‘Source2’ to make the SQL statement slightly shorter.
DELETE FROM Result WHERE EXISTS(SELECT NAME FROM Source2 WHERE Result.NAME = Source2.NAME AND Result.TYPE = Source2.TYPE)
Used with the ‘CopyRows Source1 Result’ option, this statement deletes rows from the Result table, where the NAME and TYPE columns match rows in the Source2 table.
DELETE FROM Result WHERE NOT EXISTS(SELECT NAME FROM Source2 WHERE Result.NAME = Source2.NAME AND Result.TYPE = Source2.TYPE)
Used with the ‘CopyRows Source1 Result’ option, this statement deletes rows from the Result table, that are not found in the Source2 table, based on a match of the NAME and TYPE columns.
INSERT INTO Result SELECT s1.NAME, s1.TYPE FROM Source1 s1 LEFT JOIN Source2 s2 ON s1.NAME = s2.NAME AND s1.TYPE = s2.TYPE WHERE s2.NAME IS NOT NULL AND s2.TYPE IS NOT NULL
Used with the ‘MakeTable Source1 Result’ option, this statement uses a left join to achieve the same results as the previous example.
CREATE TABLE Result (TCode nvarchar(60) NULL, Program nvarchar(40) NULL, ObjectType nvarchar(4), ObjectName nvarchar(60), Status nvarchar(20), Application nvarchar(30) NULL, ApplicationDesc nvarchar(60), DevClass nvarchar(30) NULL, DevClassDesc nvarchar(60) NULL)
INSERT INTO Result (TCode, Program, ObjectType, ObjectName, Status, Application, ApplicationDesc, DevClass, DevClassDesc)
SELECT TCODE, NAME, FOUND_TYPE, FOUND_NAME, STATUS, APPL, ATEXT, DEVCLASS, DTEXT FROM Source1
Used with no options set, the first statement creates a new table for the Result dataset. The second statement inserts rows from the Source1 table into the Result table. The purpose of this example is to copy rows to a table with more user-friendly column names.
INSERT INTO Result (SELNAME, KIND, SIGN, OP, LOW, HIGH, COUNT) SELECT Source2.TYPE, 'S', 'I', 'EQ', Source2.NAME, '', Source2.COUNT FROM Source2 WHERE Source2.TYPE = 'TCOD'
Used with the ‘CopyRows Source1 Result’ option, this statement populates the Result table with data from rows in the Source2 table where the TYPE column has the value ‘TCOD’. Note the use of literal strings (enclosed in single quotes) to provide fixed values.
INSERT INTO Result (SELNAME, KIND, SIGN, OP, LOW, HIGH, COUNT, PGMA) SELECT SELNAME, KIND, SIGN, OP, LOW, HIGH, COUNT, LOW FROM Source2
Used with the ‘CopyRows Source1 Result’ option, this statement inserts rows from the Source2 table into the Result table so that the Result table includes all the rows from the Source1 and Source2 tables.
UPDATE Result SET USERS = (SELECT COUNT(ACCOUNT) FROM Source2 WHERE Source2.TYPE = 'PROG' AND Source2.NAME = Result.PROGRAM)
Used with the ‘CopyRows Source1 Result’ option, this statement calculates the value of the Result table’s USERS column by counting matching rows in the Source2 table.
CREATE TABLE Result (TYPE nvarchar(4) NULL, TCOD nvarchar(60) NULL, PROG nvarchar(60) NULL, COUNT int, SAME int, DIFF int, IN1 int, IN2 int)
INSERT INTO Result (TYPE, TCOD, PROG, COUNT, SAME, DIFF, IN1, IN2)
SELECT Source1.SELNAME, Source1.LOW, Source1.PGMA, Source1.COUNT,
(SELECT COUNT(*) FROM Source2 WHERE Source2.PROG = Source1.PGMA AND Source2.STATUS = 'Same'),
(SELECT COUNT(*) FROM Source2 WHERE Source2.PROG = Source1.PGMA AND Source2.STATUS = 'Different'),
(SELECT COUNT(*) FROM Source2 WHERE Source2.PROG = Source1.PGMA AND Source2.STATUS = 'In1'),
(SELECT COUNT(*) FROM Source2 WHERE Source2.PROG = Source1.PGMA AND Source2.STATUS = 'In2') FROM Source1
These statements compute summary information by counting matching rows in Source1 and Source2 tables.
ALTER TABLE Result ADD TCODDESC nvarchar(36) NULL
UPDATE Result SET TCODDESC= (SELECT TTEXT FROM Result, Source2 WHERE Result.NAME=Source2.TCODE)
Used with the ‘CopyRows Source1 Result’ option, these statements add a new column to the Result table and populate it with values from the Source2 table.
ALTER TABLE Result ADD REL nvarchar(1) NULL
UPDATE Result SET Rel = 'Y'
UPDATE Result SET REL='N' WHERE EXISTS (SELECT TCODE FROM Result WHERE TCODE IN (SELECT OBJECT FROM Source2))
Used with the ‘CopyRows Source1 Result’ option, these statements add a new column to the Result table and set its value based upon matching data in the Source2 table.
SET RESULT TABLE KEYS (NAME, TYPE)
Sets the NAME and TYPE columns in the Result table to be key fields. The Result table may then be compared using the Compare Tables action.