You can use the following SQL language statement types
  • SELECT
  • WITH
  • INSERT
  • UPDATE
  • DELETE
  • EXEC
  • CREATE
  • ALTER
  • DROP

All statements can bind argument into the statements from Variables. Also, you can make dynamic statement construction by using the Variable type: SQL raw. In effect dynamically constructing the SQL statement from text.

DataETL support Statement types
Extract From: SELECT, WITH
Load Into: All other supported statement types but SELECT, WITH
WITH keyword Support for the SQL keyword WITH in conjunction with SELECT. This enables advanced result stet generation like a path hierarchy derived from a linked list of records. Useful for migration of folder structures from legacy Document Management Systems (DMS) like IBM FileNet.. This article describes more detail about selecting Parent-child relational rows.
SQLServer
syntax
with test as ( select objectid, name, parentcontainerid, CAST('' AS VARCHAR(MAX)) as path from my_new_table where parentcontainerid = '' or parentcontainerid is null union all select my_new_table.objectid, my_new_table.name, my_new_table.parentcontainerid, CAST(CASE WHEN test.path = '' THEN(CAST(test.name AS VARCHAR(MAX))) ELSE(test.path + '\' + CAST(test.name AS VARCHAR(MAX))) END AS VARCHAR(MAX)) as path from my_new_table, test where my_new_table.parentcontainerid = test.objectid) select name, objectid, path, CASE WHEN path = '' THEN name ELSE path + '\' + name END as fullpath from test;
Firebird
syntax
with recursive test as ( select objectid, name, parentcontainerid, CAST('' AS VARCHAR(2000)) as path from my_new_table where parentcontainerid = '' or parentcontainerid is null union all select my_new_table.objectid, my_new_table.name, my_new_table.parentcontainerid, CAST(CASE WHEN test.path = '' THEN(CAST(test.name AS VARCHAR(2000))) ELSE(test.path || '\' || CAST(test.name AS VARCHAR(2000))) END AS VARCHAR(2000)) as path from my_new_table, test where my_new_table.parentcontainerid = test.objectid) select name, objectid, path, CASE WHEN path = '' THEN name ELSE path || '\' || name END as fullpath from test;
SQL Server stored procedure support. You can call stored procedures using the standard syntax like below. Note that you can pass in variables as arguments.
Exec MyStoredProcedure([<My Variable argument>],'My other argument');
Limitation: You cannot use Transact SQL stored procedure syntax directly in the script. You must create the procedure in the database and then call it.
SQL Server UNICODE support INSERT/UPDATE SQL statements can use the SQLServer specific UNICODE syntax N'my string value' to support UNICODE character set for columns of type NVARCHAR. Notice tables columns created from the IDE will be of type VARCHAR. So, to support UNICODE you must create/alter your table using embedded SQL or use SQL Server Management Studio.

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment