SQL Statement Insert Writer

Creating SQL Command Files for Creating and Filling Tables

The plain SQL plugin can create SQL Server compatible SQL Command files for

• Dropping the named table if it exists
• Creating a table according to a table definition
• Filling the table via INSERT statements

This can be useful for various scenarios where it's needed to have SQL statements for portability instead of directly working with the database connection.

In order to create SQL statements as an output, use the following syntax:

<Target config="[configuration]">file://<target file name>.sql</Target>

The actual trigger is the .sql suffix here.

The configuration needs to be the file name of an SQL table definition XML file in the following syntax:

<config>
    <table name="table_name">
        <col name="field1" type="Int" />
        <col name="field2" type="UniqueIdentifier" />
        <col name="field3" type="NVarChar(255)" />
    </table>
</config>

Whatever is stated in the type attributes will be passed on to the SQL engine as a field type. Reasonable effort is made to match the output strings to whatever type is in the table field definitions (using quotes for strings, dates, etc., and not quoting numeric types).

The names of the fields must match the output fields in the transformation definition. If fields cannot be mapped, the SQL Writer will insert NULL values as a default.

Note: There is currently no support for creating e.g. nullable field definitions. This feature is mostly intended for mass inserting data into temporary tables for further processing within the SQL engine!

Misc

• A BEGIN/COMMIT statement is automatically inserted every 1000 INSERT statement; this typically speeds up the inserting process
• If you provide a parameter sqltimestamp=true when calling nft, the SQL Writer will insert an SQL comment with the current date time into the generated file
• The generated SQL statements have been tested foremost with SQL Server, but should work on PostgreSQL or MySQL as well, depending on the provided field types